I recently found some code that will generate a value or ID based on the last 2 digits of a year field followed by a 4 digit sequential serial number.
Here is the code:
txtDateCreated has a default value of =Date()
This code will return a value like 19-0001, 19-0002 etc.
I understand most of the code except for this:
I understand that this is the criteria of the DMax function, but I really don't understand the syntax. The main thing I'm not getting is the \*\' after yy. I understand that the \ character is used to precede a literal character, but how are the * and ' functioning?
I also don't understand this
It appears that it is saying that [YearNumber] should follow a pattern (Like) but then it appears to be concatenating the format function.
Would anyone mind shedding some light on this for me? I've been staring at this all day! :banghead:
Here is the code:
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[YearNumber]", "[tblData]", "[YearNumber] LIKE '" & Format([txtDateCreated], "yy\*\'"))
If IsNull(vLast) Then
iNext = 1
Else: iNext = Val(Mid(vLast, 4)) + 1
End If
Me![YearNumber] = Format([txtDateCreated], "yy") & "-" & Format(iNext, "0000")
End Sub
This code will return a value like 19-0001, 19-0002 etc.
I understand most of the code except for this:
Code:
"[YearNumber] LIKE '" & Format([txtDateCreated], "yy\*\'")
I also don't understand this
Code:
LIKE '" &
Would anyone mind shedding some light on this for me? I've been staring at this all day! :banghead: