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:

   ARTICLE-ID: Q182568
   TITLE     : ACC97: Microsoft Access 97 Sample Queries Available on
               MSL

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.

The following example is based on the Employees table from the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

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.

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

   by commas, into the parameter value dialog box:

   Fuller,King,Callahan

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 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)

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

3. Create a new query based on the Customers table. Drag any fields

   that you want 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.

   a Text data type:

      Enter ID list using commas and no spaces:

   of parameters. The following message is displayed in the dialog box:

      Enter ID list using commas and no spaces:

      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           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto