Help with Format function syntax (1 Viewer)

[DataBass]

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:

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

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:

 "[YearNumber] LIKE '" & Format([txtDateCreated], "yy\*\'")

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

LIKE '" &

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:

 

[isladogs]

Functionally the filter criteria means exactly the same as this simpler version

vLast = DMax("[YearNumber]", "[tblData]", "Left([YearNumber],2) = Format([txtDateCreated], "yy")")

 

[Cronk]

vLast = DMax("[YearNumber]", "[tblData]", "[YearNumber] LIKE '" & Format([txtDateCreated], "yy\*\'"))

is equivalent to

vLast = DMax("[YearNumber]", "[tblData]", "Left([YearNumber],2) Like '" & Format([txtDateCreated], "yy") & "*'")

That is, the use of back slash in the format function is to insert a literal character, or your case the 2 characters '*

 

[DataBass]

Thanks Isladogs and Cronk, that is very helpful.

Your simplified examples make much more sense.

I'm going to compare them to the original code and see if I can make sense of it.