ACC2000: How to Create a Parameter In() Statement

Article translations Article translations
Article ID: 210530 - View products that this article applies to.
This article was previously published under Q210530
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

On This Page

SUMMARY

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: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
207626 ACC2000: Access 2000 Sample Queries Available in Download Center
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

MORE INFORMATION

Method 1

The following method demonstrates how to use the Instr() function to pass multiple values, separated by commas, to a parameter query.

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.

  1. Start Microsoft Access, and then open the sample database Northwind.mdb.
  2. Create the following new query based on the Employees table:
    Query: ParamQuery
    Type: Select Query

    Field: EmployeeID
    Table: Employees
    Show: True

    Field: LastName
    Table: Employees
    Show: True
  3. In the next empty column on the query grid, type the following expression and attributes:
    Field: InStr([Last Names separated by commas,Blank=All],[LastName])
    Criteria: > 0 Or Is Null
    Show: False
    Save the query as ParamQuery.
  4. On the View menu, click Datasheet View, and then type the following last names, separated by commas, into the Enter Parameter Value dialog box:
    Fuller,King,Callahan
NOTE: Three records are returned. If you leave the parameter blank, the query returns all records.

Method 2

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.

  1. Start Microsoft Access, and then open the sample database Northwind.mdb.
  2. 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
    					
  3. Close and save the module.
  4. Create a new query based on the Customers table. Drag any fields that you want to the query grid.
  5. 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.
  6. 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:
  7. 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???")

REFERENCES

For more information about the In operator, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type in operator example (dao) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 210530 - Last Review: June 23, 2005 - Revision: 3.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbinfo kbusage KB210530

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com