ACC2000: Using Variables in the Criteria Row of the Design Grid (210388)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210388
Moderate: Requires basic macro, coding, and interoperability skills.

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

SUMMARY

You cannot use a variable in the Criteria row of the design grid because Microsoft Access assumes that the variable name is a literal text string. However, you can use a function name in the design grid and have the function return the value of the variable.

MORE INFORMATION

To create a query that uses the value of a global variable as a criteria, follow these steps:
  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. Create a new query based on the Employees table. Drag the FirstName, LastName, Address, and City fields to the design grid.
  3. In the criteria cell of the LastName column, type the following function name:

    ReturnVariable()

  4. Save the query as Variable Criteria Query, and then close it.
  5. Create a new module and type the following line in the Declarations section if it is not already there:

    Option Explicit

  6. Type the following function in the module: 
    Function ReturnVariable()
   ' MyVar could also be a global variable set in
   ' a different function.
   Dim MyVar as String
   MyVar = "Davolio"
   ReturnVariable = MyVar
End Function
  7. Save the module as Variable Criteria Module, and then close it.
  8. Open Variable Criteria Query, which you created in step 4.

    Note that only the record for Nancy Davolio appears.
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbhowto kbinfo kbusage KB210388
©2004 Microsoft Corporation. All rights reserved.