At the end of last year I got some help with creating an automated incrementing field. It was working great until it became 2001. The field is supposed to show the letter “E” and then two digits for the year (01) and then the day of the year in three digits, for example today would be 054 because it’s the 54th day of the year, and then the number of the entry using three digits. This is the code I’m using:
Dim sPrefix As String
sPrefix = “E” & Format(Date, “yyy”)
[RefNmbr] = sPrefix & Format(Val(Mid(Nz(DMax(“[RefNmbr]”, “[tblRequests]”, “Left([RefNmbr],6)='” & sPrefix & “‘”), 0), 7)) + 1, “000”)
It was working fine when the number of the day of the year was three digits, like 256. But now that the year is less than 100 days old it’s not working. For example, the first one for today reads E0154001 which won’t work for incrementing because the day of the year is only showing 2 digits.
Any suggestions?
Thanks.
