Display Max Ref No if preceded by F / J (can I "max if")

[jdlantz]

I need something like a "max if", that is, to return the max value if the number is preceded with a specific letter.

Our reference number system is a 5 character system: 1 letter + 4 numbers. The letter indicates the type of document being referenced, and the numbers start at 1 and count up within each letter range. (So it is acceptable to have both F0001 and J0001.) We want to use only one reference field although we may have many letter codes. On the data entry form, however, I want to display the next available number for certain letter codes. Here I am using "F" and "J" so my reference field might have these entries:

F0077
J0046
F0078
J0047
F0079

And I would want my form to have two boxes displaying F0080 and J0048.

If it helps, I had a similar Excel spreadsheet and got some help from another forum to build an array formula below:
{="F"&TEXT(MAX(--RIGHT(L$7,4),IFERROR(--SUBSTITUTE(UPPER($K$13:$K$3002),"F",""),0))+1,"000 0")}

At the moment I'm trying to use some sort of combination of IIF() and DMax() with Left() and/or Right(), but I'm not getting it.

Any help is appreciated!!

[June7]

Generating custom unique identifier is common topic. Search forum.

Try:

"F" & Format(Mid(Nz(DMax("fieldname", "tablename", "Left([fieldname],1)='F'"),"F0"),2) + 1, "0000")

"J" & Format(Mid(Nz(DMax("fieldname", "tablename", "Left([fieldname],1)='J'"),"J0"),2) + 1, "0000")

[jdlantz]

Thank you very much, it worked!

I apparently wasn't using the right search terms but I will check out more about unique identifiers.