Find a record by numbers and letters

[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

 

[Guest]

Sorry, to make the names more meaningful

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

 

[Guest]

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

 

[John Spencer]

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
..