This article demonstrates two methods that you can use to pass multiple
values to a query by using a parameter prompt. The first method uses the
Instr() function to parse the parameter values entered when the query is
run. The second method describes a way to create a query by using the In()
operator with multiple values.
NOTE: A demonstration of the technique used in this article can be seen
in the sample file, Qrysmp97.exe. For information about how to obtain
this sample file, please see the following article in the Microsoft
ACC97: Microsoft Access 97 Sample Queries Available in Download Center
The following method demonstrates how to use the Instr() function to pass
multiple values, separated by commas, to a parameter query.
The following example is based on the Employees table from the sample
database Northwind.mdb (or NWIND.MDB in version 2.0).
- Open the sample database Northwind.mdb.
- Create the following new query based on the Employees table and save
the query as ParamQuery:
NOTE: In Microsoft Access 2.0, there is a space in the Employee ID
and Last Name field names.
Type: Select Query
- In the next empty column, enter the following expression and
Field: InStr([Last Names separated by commas,Blank=All],[LastName])
Criteria: > 0 Or Is Null
- Switch to Datasheet View and enter the following last names, separated
by commas, into the parameter value dialog box:
NOTE: Three records are returned. If you leave the parameter blank, the
query will return all records.
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
enter a list of values. The function processes the user's entries as the list of multiple parameters for the In() operator.
This part of the article assumes that you are familiar with Visual Basic
for Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0
The following example is based on the sample database Northwind.mdb (or
NWIND.MDB in versions 1.x and 2.0)
- Create a new module with the following two functions:
'Declarations section of the module.
' 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)
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
GetToken = stToken
' 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
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
InParam = 0
- 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.
NOTE: In versions 1.x and 2.0, there is a space in the Customer ID
FieldName: InParam([CustomerID],[ Enter ID list using commas and no _
NOTE: The value InParam(...) shown for the FieldName should be
entered as one statement on a single line. The InParam() function
works with Integer fields as well as with Text fields.
- On the Query menu, click Parameters. Enter the following parameter with
a Text data type:
Enter ID list using commas and no spaces:
- Click OK and run the query. Note that you are prompted to enter a list
of parameters. The following message is displayed in the dialog box:
Enter ID list using commas and no spaces:
- In versions 97, 7.0, and 2.0, type:
In version 1.x type:
All records meeting the criteria are displayed.
NOTE: One limitation of the In()operator is that it does not support
wildcards, such as * or ?. For example, Microsoft Access cannot run
IN("A*", "BON*", "CRATE???")
For more information about the In operator, search the Help Index for "In
Article ID: 100131 - Last Review: January 18, 2007 - Revision: 2.3
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
|kbhowto kbprogramming kbusage KB100131|Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.