ACC: How to Create a Parameter In() Statement

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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 Knowledge Base:

Q182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center

MORE INFORMATION

Method 1

1. Open the sample database Northwind.mdb.



2. Create the following new query based on the Employees table and save the query as ParamQuery:

   
         Query: ParamQuery
         --------------------
         Type: Select Query
   
         Field: EmployeeID
            Table: Employees
            Show: True
         Field: LastName
            Table: Employees
            Show: True 

   NOTE: In Microsoft Access 2.0, there is a space in the Employee ID and Last Name field names.



3. In the next empty column, enter the following expression and attributes:

   
         Field: InStr([Last Names separated by commas,Blank=All],[LastName])
         Criteria:  > 0 Or Is Null
         Show: False 




4. Switch to Datasheet View and enter the following last names, separated by commas, into the parameter value dialog box:

   Fuller,King,Callahan

Method 2

1. 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 




2. Close and save the module.



3. Create a new query based on the Customers table. Drag any fields that you want to the query grid.



4. 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.

   
         FieldName: InParam([CustomerID],[ Enter ID list using commas and no _
                    spaces:])
         Show: False
         Criteria: True 

   NOTE: In versions 1.x and 2.0, there is a space in the Customer ID field name.
   
   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.



5. On the Query menu, click Parameters. Enter the following parameter with a Text data type:

   Enter ID list using commas and no spaces:




6. 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:




7. In versions 97, 7.0, and 2.0, type:

   CHOPS,EASTC,FAMIA

   In version 1.x type:

   BLUEL,CACTP,DOLLC

   All records meeting the criteria are displayed.

   IN("A*", "BON*", "CRATE???") 

REFERENCES

For more information about the In operator, search the Help Index for "In operator."

Additional query words: queries

Keywords : kbusage QryParm
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto