Search for Multiple Parameters

Public Sub GetStuff()
'*******************************************
'Purpose:   Driver to create a QueryDef
'           that will return table, field,
'           items to search as prompted/
'           entered by the operator.
'Coded by:  raskew
'Calls:     Function MultiParms
'*******************************************
Dim pTable As String
Dim pField As String
Dim pStuff As String
Dim strSQL As String
Dim qd     As QueryDef

pTable = InputBox("Enter Table/Query name", "Enter Source")
pField = InputBox("Enter field to search", "Enter Field")
pStuff = InputBox("Enter items to search for -- separate by comma (no spaces)", "Enter Items")
strSQL = "Select [" & pTable & "].* From [" & pTable & "] WHERE " & MultiParms(pStuff, pField)
Debug.Print strSQL

'Create QueryDef
   Set qd = CurrentDb.CreateQueryDef("qryTemp", strSQL)
    
'Open / view new query
   docmd.OpenQuery qd.name
    
'Delete QueryDef since this is just an example
   CurrentDb.QueryDefs.Delete qd.name

End Sub

Public Function MultiParms(pStr As String, pField As String) As String
'*******************************************
'Purpose:   Called by GetStuff() and returns
'              the WHERE portion of a query SQL
'              based on input by the operator.
'Coded by:  raskew
'Calls:       Function StrCount()
'*******************************************
Dim astr()   As Variant
Dim strHold  As String
Dim strInt   As String
Dim strKeep  As String
Dim itemHold As String
Dim stp      As String
Dim i        As Integer
Dim k        As Integer

strHold = pStr
itemHold = ","
strInt = StrCount(strHold, itemHold)
'k = Int(Left(strInt, InStr(strInt, itemHold) - 1))
k = strInt + 1
ReDim astr(1 To k) As Variant
For i = 1 To k
   If i <= k - 1 Then
      astr(i) = Left(strHold, InStr(strHold, itemHold) - 1)
      strHold = Mid(strHold, InStr(strHold, itemHold) + 1)
   Else
      astr(i) = strHold
   End If
Next i
strHold = ""
For i = 1 To k
    strHold = astr(i)
    stp = Switch(IsNumeric(strHold), "", IsDate(strHold), "#", True, "'")
    strKeep = strKeep & "inStr([" & pField & "]," & stp
    strKeep = strKeep & astr(i) & stp
    strKeep = strKeep & ")>0"
    If i < k Then strKeep = strKeep & " OR "
Next i

MultiParms = strKeep & ";"
End Function

Function StrCount(ByVal TheStr As String, theItem As Variant) As Integer
'------------------------------------------------------------------
' Purpose:   Counts number of times item occurs in a string.
' Coded by:  raskew
' Arguments: TheStr: The string to be searched.
'            TheItem: The item to search for.
' Returns:   The number of occurences as an integer.
'
' Note: To test:   Type '? StrCount("The quick brown fox jumped over
'                  the lazy dog", "the") in the debug window.
'                  The function returns 2.
'------------------------------------------------------------------
Dim j         As Integer
Dim placehold As Integer
Dim strHold   As String
Dim itemHold  As Variant

    strHold = TheStr
    itemHold = theItem
    j = 0
    
    If InStr(1, strHold, itemHold) > 0 Then
       While InStr(1, strHold, itemHold) > 0
          placehold = InStr(1, strHold, itemHold)
          Debug.Print placehold
          j = j + 1
          strHold = Mid(strHold, placehold + Len(itemHold))
       Wend
    End If
    StrCount = j
End Function