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 describes a way to create a query by using the In() operator with multiple values.

The method shown below uses a query that calls a function, which is passed 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 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

MORE INFORMATION

The following example is based on the sample database Northwind.mdb (or Nwind.mdb in versions 1.x and 2.0)

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

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