Access Cookbook

Use a VBA Variable to Filter a Query

Problem

You’d like to be able to return rows in a query that have a test score greater than a specified value, which is stored in a VBA variable. When you try to use the variable in the query design grid, Access thinks it’s a literal value. Is there some way to get queries to understand VBA variables?

Solution

To use a VBA variable in a query, you need to write a VBA function that returns the value of the variable as its return value and then reference the VBA function either as part of a calculation or in the criteria of a field. The only way to work with VBA in queries is to call a function. This solution shows you how to do that.

In the sample database 01-06.MDB you’ll find tblScores, a table of names and test scores. The goal of the sample is to allow you to specify a cutoff value and list everyone whose scores are greater than that value.

Open the frmScores form. This form allows you to choose between a randomly selected cutoff value and a user-specified cutoff value. If you choose the user-specified cutoff value, a text box is made visible to allow you to enter the cutoff value. When you click on the “Show the results” command button, an event procedure runs that saves the cutoff value—either the randomly chosen cutoff or the user-specified cutoff—to a public variable and then runs the qryScores query.

The qryScores query references the public variable using the GetCutoff function and then returns the rows in tblScores in which the score ...