Search on Multiple Parameters

[raskew]

Hi -

I've submitted the following to the Code Repository but have yet to get a response 'Yea' or 'Nay'. So, let me post it here so it might be helpful to someone.
'*******************************************


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 .

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 used 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 a query SQL, opens a temporary 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;

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 = 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

 

[boblarson]

The post in the Repository has been approved and is open now.

 

[raskew]

Bob Larson -

Thanks for the update.

Best Wishes - Bob

 

[DCrake]

Simple Software Solutions

Access has an inbuilt function called BuildCriteria. If you are unsure how to format your sql you can use this function and let access decide the best way for you.

Example
The following example prompts the user to enter the first few letters of a product's name and then uses the BuildCriteria method to construct a criteria string based on the user's input. Next, the procedure provides this string as an argument to the Filter property of a Products form. Finally, the FilterOn property is set to apply the filter.

Sub SetFilter()
    Dim frm As Form, strMsg As String
    Dim strInput As String, strFilter As String

    ' Open Products form in Form view.
    DoCmd.OpenForm "Products"
    ' Return Form object variable pointing to Products form.
    Set frm = Forms!Products
    strMsg = "Enter one or more letters of product name " _
        & "followed by an asterisk."
    ' Prompt user for input.
    strInput = InputBox(strMsg)
    ' Build criteria string.
    strFilter = BuildCriteria("ProductName", dbText, strInput)
    ' Set Filter property to apply filter.
    frm.Filter = strFilter
    ' Set FilterOn property; form now shows filtered records.
    frm.FilterOn = True
End Sub

Code taken from Access help.

CodeMaster:

 

[raskew]

Hi -

That's interesting. Have a few inherited procedures that have used it. So how would you use it if you wanted to return all Customers records where field [CompanyName] contained either 'grocer', 'market' or 'store'?

Bob