Search for Multiple Parameters

[raskew]

Many times we need to search a table for multiple values. These three
functions/subs, copied to a standard module, will provide that ability.

To use, from the debug (immediate) window type: GetStuff <enter>. You'll be prompted for:
1) Table name
2) Field name
3) Items to search for, which you'll enter with commas separating the items, e.g.
Say you were searching Northwind's Customers table and wanted all records that contained 'grocer' or 'store' or 'market'
you'd enter grocer,store,market. No spaces between items!

The process handles text, dates, numbers, currency and Yes/No fields. No
need to surround dates with # # or text with " ", the function
will do it for you. With yes/no fields, you'd enter -1 for true or 0 for False.
I use US short-date format, e.g. 7/15/08,8/01/08 You'll need
to experiment if using other date formats.

Once entered, the process creates/opens a temporary QueryDef query and prints the query SQL to the debug window:
Select [customers].* From [customers] WHERE inStr([companyname],'grocer')>0 OR inStr([companyname],'market')>0 OR inStr([companyname],'store')>0;

If you want to make the query permanent (being temporary, the queryDef is
automatically deleted as soon as the query opens) just copy the SQL from the debug window and paste to a new query.

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

'*********************************************************

Once again, just copy/paste the three procedures to a standard module
then, from the debug (immediate) window type GetStuff<enter>.

You'll be prompted for table name, field name, items to search for. I don't
know if there's a limit on the number of items, I've tried up to 10 without
problems.

Hope you'll find it as useful as I have.

Best Wishes - Bob