Expression

[Jason]

I am looking to create an expression for a table that will
automatically generate a number. I require the format to
be in YYMM### format. I was able to establish both the
year and month portion but am having difficulty with the
numerical portion. The ### will begin at 001 and increase
by one with each new record. The ### will need to reset
to 001 at the beginning of each month. All help will be
appreciated.

 

[Wayne Morgan]

Assuming the table to be Table1 and the field to be Field1, a user defined
function such as this one should do it.

Public Function CodeNumber () As String
Dim intNumberToIncrement As Integer
intNumberToIncrement = Nz(DMax("CInt(Right([Field1],3))", "Table1",
"Left([Field1],4)='" & Format(Date, "yymm") & "'"), 0) +1

CodeNumber = Format(Date, "yymm") & Format(intNumberToIncrement, "000")
End Function

There is no check here to see if you exceed 999. You would want to add that.