Create query criteria from multiple records with wildcards 1

[Poduska]

I have a query with about 40 statements like this

Like "XXX*"

For example

Not Like "9?9*" And Not Like "499*" And Not Like "835*" And Not Like "432*" And Not Like "47*" And ....

The entire statement is together as criteria against one field. The number of characters (X's) and the value of the characters change but all have a wildcard character at the end.

I want to maintain all 40 of these criteria as records in a table so the end user can maintain them (add new, etc)instead of having me make the changes or be asked what is there.

A simple table like
Value
432*
47*
9?9*

I can create and call queries in VBA so that is an option.

[red]Anyone have any cleaver ideas as to how I might even start?[/red]

Thanks

 

[MajP]

if creating in code then

Public Function getLike(fldName As String, Optional AndOR As String = "AND", Optional NotLike As Boolean = True) As String
  Const tblName = "tblCriteria"
  Const critFldName = "strCriteria"
  Dim rs As DAO.Recordset
  Dim likeOrNot As String
  
  Set rs = CurrentDb.OpenRecordset(tblName)
  If NotLike Then
    likeOrNot = "NOT LIKE"
  Else
    likeOrNot = "LIKE"
  End If
  Do While Not rs.EOF
     If getLike = "" Then
       getLike = fldName & " " & likeOrNot & " '" & rs.Fields(critFldName) & "'"
     Else
       getLike = getLike & " " & AndOR & " " & fldName & " " & likeOrNot & " '" & rs.Fields(critFldName) & "'"
     End If
     rs.MoveNext
  Loop
  getLike = "WHERE " & getLike
End Function

Public Sub testLike()
  Debug.Print getLike("SomeField")
  Debug.Print getLike("SomeField", , False)
  Debug.Print getLike("SomeField", "OR", True)
  Debug.Print getLike("SomeField", "OR", False)
End Sub

results

WHERE SomeField NOT LIKE '432*' AND SomeField NOT LIKE '47*' AND SomeField NOT LIKE '9?9*'
WHERE SomeField LIKE '432*' AND SomeField LIKE '47*' AND SomeField LIKE '9?9*'
WHERE SomeField NOT LIKE '432*' OR SomeField NOT LIKE '47*' OR SomeField NOT LIKE '9?9*'
WHERE SomeField LIKE '432*' OR SomeField LIKE '47*' OR SomeField LIKE '9?9*'

 

[Poduska]

Wow, Will try first thing in the morning but it appears to be exactly what I need.

I LOVE the idea of using a function, makes it portable.

Thanks