Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 08-08-2008, 05:47 PM   #1
raskew
AWF VIP
 
Join Date: Jun 2001
Location: Foothills of the Ozarks, USA
Posts: 2,734
Thanks: 1
Thanked 16 Times in 15 Posts
raskew has a spectacular aura about raskew has a spectacular aura about raskew has a spectacular aura about
Search for Multiple Parameters

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.

Code:
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
'************************************************* ********

Code:
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
'************************************************* ********

Code:
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

raskew is offline  
The Following User Says Thank You to raskew For This Useful Post:
rcsfca (02-19-2011)
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced Film Search Jk45 Forms 3 01-15-2008 12:03 PM
Can't call qry as search filter!!?? Access_guy49 Forms 0 01-15-2008 06:56 AM
Search box keeps bringing up errors anonymous user General 1 11-03-2006 04:06 PM
need help with search textbox anonymous user Forms 0 11-02-2006 04:49 PM




All times are GMT -8. The time now is 05:39 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World