Windows 10 
Access 2010 64bit
Access 2010
Please help I have searched every where for a multi keyword search that can search within a string, not just exact matches. I wish I was knowledgeable enough to create on my own but I am still a newbie.
I found the below code on a MS site. it allow for multiple word search with a comma delimit. Perfect!! However the keywords must be an exact match. Can the below code be edited to allow for a search of multiple words within the string.
search as if it is using like * word *
example.
search for CHOPS,EASTC,FAM
and it finds records for CHOPS,EASTC,FAMIA
The following method uses a query that calls a function and passes it two parameters. The first parameter is the name of a field that exists in the table on which the query is based. The second parameter prompts the user to type a list of values. The function processes the user's entries as the list of multiple parameters for the In() operator.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
Start Microsoft Access, and then open the sample database Northwind.mdb.
Create a new module with the following two functions:
'************************************************* ***********
'Declarations section of the module.
'************************************************* ***********
Option Explicit
'================================================= ===========
' The GetToken() function defines the delimiter character.
'================================================= ===========
Function GetToken (stLn, stDelim)
Dim iDelim as Integer, stToken as String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function
'================================================= ===========
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the In() operator.
'================================================= ===========
Function InParam (Fld, Param)
Dim stToken as String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function
Close and save the module.
Create a new query based on the Customers table. Drag any fields that you want to the query grid.
Add the following field to the query grid:
NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.
Field Name: InParam([CustomerID],[ Enter ID list using _
commas and no spaces:])
Show: False
Criteria: True
NOTE: The value InParam(...) shown for the Field Name should be typed as one statement on a single line. The InParam() function works with Integer fields as well as with Text fields.
Run the query. Note that you are prompted to type a list of parameters. The following message is displayed in the dialog box:
Enter ID list using commas and no spaces:
In the Enter Parameter Value box, type:
CHOPS,EASTC,FAMIA
Notice that all records that meet the criteria are displayed. If you type no parameters, no records are returned.
NOTE: One limitation of the In() operator is that it does not support wildcards, such as * or ?. For example, Microsoft Access cannot run the following query:
IN("A*","BON*","CRATE???")
Reply With Quote
