Find a record by numbers and letters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Project field,a primary key, contains 2-5 numbers with a 2 letter suffix, the
users initials. When the user clicks a form control to add a new record, I'm
trying to find the last record with their initials to help increment the
project number for the new record. I can get the users initials, but the
docmd.find or goto commands aren't allowing me to seek locate previous
records to get their project numbers. Thoughts on the workaround appreciated.
TIA
 
Sorry, to make the names more meaningful

DMax("Val(Mid([FieldName],3))" , "TableName" , "Left([FieldName],2)='" &
Initials & "'")
 
Try something like

DMax("Val(Mid([Field],3))" , "Items" , "Left([Field],2)='" & Initials & "'")

So if you have
AA1
AA2
AA3

And the Initials value is "AA" it will return 3, all you need is to add 1 to
it
 
Perhaps the following expression would give you what you want

NZ(DMax("Val(ProjectField)","YourTable","ProjectField Like ""*" &
txtUserInitials & """"),0)

I am guessing that your Project field has values like
1JP
232JP
1056JP

Of course if your field has leading zeroes then you could have a problem if
you would have something like these values for JP, since you would not be
able to distinguish which was the "max" by the above.
003JP
3JP
00003JP

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top