Assuming that you have all prior trail numbers put in the table as text fields with 2-digit months and 9-digit field length, the DMAX should get the latest one added.
Ranman's code will get the 3-digit code on the end, but won't tell you if that code is for this month or last month.
Then you need to parse to make sure that the month and year are the same as current. Alternately, you could build the YY-MM- part of the key and use the LIKE keyword with the DCOUNT verb.
That would look something like this:
Code:
function getNextPI()
dim strYYMM As String
dim intCnt As Integer
strYYMM = format(date(),"yy") & "-" & format(date(),"mm") & "-"
intCnt = dCount("[piNum]","table","PiNum] LIKE '" & strYYMM & "%'" ))
intCnt = intCnt +1
getNextPI = strYYMM & text(intCnt,"000")
end function
Warning - this function counts how many trails has been assigned, it doesn't test whether they were sequential. You might want to add a test in there to make sure that the one assigned is not already on file.