Criteria w/Wildcards

[CharlieT302]

Hi,

The code below is designed to create a ID from various fields such as names and dates. This results in duplicates...which it is appropriate. However if a duplicate exists, the code assigns a number at the end to make it unique. It does this well.

However, when the code searches (it actually counts) for duplicates, I want it to search only the base code, regardless of the number placed at its end.

I would like to use a "wildcard" to accomplish this. How do I add a wildcard to the criteria?

Thanks

Sample Code:
************************************************************
Dim strNumber As String
Dim strCriteria As String

strNumber = Me.[Artist First Name] & Left(Me.[Artist Full Last Name], 1) & DatePart("m", Me.Date_Painting_Started) & DatePart("d", Me.Date_Painting_Started) & Format(DatePart("yyyy", Me.Date_Painting_Started), "yy") & Left(Me.Discipline, 2)

strCriteria = "[Tracker Painting Number] = '" & strNumber & "'"


If (DCount("*", "tbl_Tracker_Information_sub", strCriteria)) > 0 Then

Me.Tracker_Painting_Number.Value = Me.[Artist First Name] & Left(Me.[Artist Full Last Name], 1) & DatePart("m", Me.Date_Painting_Started) & DatePart("d", Me.Date_Painting_Started) & Format(DatePart("yyyy", Me.Date_Painting_Started), "yy") & Left(Me.Discipline, 2) & (DCount("*", "tbl_Tracker_Information_sub", strCriteria) + 1)


GoTo exit_sub
Else
Me.Tracker_Painting_Number.Value = Me.[Artist First Name] & Left(Me.[Artist Full Last Name], 1) & DatePart("m", Me.Date_Painting_Started) & DatePart("d", Me.Date_Painting_Started) & Format(DatePart("yyyy", Me.Date_Painting_Started), "yy") & Left(Me.Discipline, 2)
GoTo exit_sub
End If

 

[dhookom]

If I understand correctly, you want:

strCriteria = "[Tracker Painting Number] LIKE '" & strNumber & "*'"

Duane
Hook'D on Access
MS Access MVP

 

[CharlieT302]

Thank you Dhookum

 

[CharlieT302]

The wildcard worked fine. I have another question regarding the wildcard. The [Tracker Painting Number] field will store the combined field values in the "strNumber" string in addition to a number.

There could be four entries with the exact same ID but with a different number at the end (either a one or a two digit). Ex: SallyM102910PA(1) or SallyM102910PA(12)

Note: the parenthesis around the numbers are just to highlight the part I wish to work with. They are not in the actual value.

I would like to be able to find the record with highest ending number. The ultimate purpose being to ensure the next available number is being assigned.

Can a "max" function (or something similar) be applied to the part of the number we found with the wildcard in, say, a lookup function?

 

[dhookom]

I would store the number in its own field and make it numeric.

Duane
Hook'D on Access
MS Access MVP

 

[CharlieT302]

Yeah, I was thinking that. What if that is not an option, for whatever reason.

We can obviously do a search with the ending number being represented by a wildcard. Is there a way of isolating the ending number from the rest of the field in a "max" or some similar function?

 

[dhookom]

You would need to accurately describe the values such as the number of characters, are there always 1 or 2 digits, is the character prior to the numbers always a letter, ...

Duane
Hook'D on Access
MS Access MVP