I have a database that I use a function to create inspection report number by setting the date in a specific format and extension number on the end. Here is what I have so far:
Public Function NextAutoNum() As String
Dim I As Integer
Dim D As Date
Dim MySQL As String
D = DLookup("[LastDate]", "tblICNNumber")
I = DLookup("[LastNumber]", "tblICNNumber")
If D = Date Then
I = I + 1
D = Date
I = 1
MySQL = "Update tblICNNumber SET[LastDate] = Date()"
MySQL = "Update tblICNNumber SET[LastNumber] = " & I & ""
NextAutoNum = Format$(Year(D), "0000") & Format$(Month(D), "00") & Format$(Day(D), "00") & "-" & Format$(I, "000")
It will give me an inspection number in a format such as 20090424-001 for the first inspection conducted on the 24th of April. It works great as longer as inspections are inputted on time, but if a inspection is inputted several days later, I don't know how to draw the last extension number inputted on the previous date. I could just continue to assign the control corresponding to the date the inspection was inputted, but I would like to have the control correspond to the date the inspection was performed. Any ideas???