ACC: Using Variables in the Criteria Row of the QBE Grid

ID: Q114511

The information in this article applies to:

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

SUMMARY

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

You cannot use a variable in the Criteria row of the Query by Example (QBE) grid because Microsoft Access will assume that the variable name is a literal text string. However, you can use a function name in the QBE grid and have the function return the value of the variable.

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 shows you how to create a form based on a query that uses the value of a global variable as a criterion:

1. Open the sample database Northwind.mdb (NWIND.MDB in Microsoft Access

   2.0 and 1.x).

2. Create a new query based on the Employees table. Drag the FirstName,

   LastName, Address, and City fields to the QBE grid.

   NOTE: In versions 1.x and 2.0, there is a space in First Name and
   Last Name field names.

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 the Variable Criteria Query created in step 4. Note that only

   the record for Nancy Davolio appears.

Additional query words: parameter

Keywords          : kbusage QryHowto QryCrit 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo

Last Reviewed: November 21, 1998