Many of my clients request a lot of flexibility in the criteria they can use to print a report. I give them a selection screen with combo boxes to allow them to choose the criteria that will control the data in their reports. I used to check whether a selection had been made and then string together a Where clause for the SQL statement that contained each criterion they’d entered. This can become very tedious when a form has multiple selection criteria. I’m now using a much simpler method: For each criterion I just create a global variable and function.

A simple selection form, for instance, might contain a “client code” combo box and a “state code” combo box. The users may select by client alone, by state alone, or by state and client. To support the client code combo box, I dimension a global variable called gvClientCode as a variant. I then create a function called GetgvClientCode that returns either an asterisk (*) if no value has been selected in the combo box, or the value in the combo box if it’s been set:

Public Function GetgvClientCode()

If IsNull(gvClientCode) Or _
   gvClientCode = "" Then
    GetgvClientCode = "*"
 Else
    GetgvClientCode = gvClientCode
 End If

End Function

I call the appropriate function in the AfterUpdate of each combo box on the selection screen. I also set all global variables to Null when loading the selection screen and frequently provide buttons on the selection screen to clear all combo boxes and set all of the global variables to Null.

Each report’s Row Source property is based on a stored query. The criteria row for each field in the query calls my functions. In the query, the ClientCode field’s criteria row contains the test:

Like GetgvClientCode()

If client code has been selected on the selection screen, the query is filtered by client code. Otherwise, the query will return all clients because the value returned from the function is the asterisk, giving:

Like *

I can have as many of these global variables/functions/ query criteria as I want or need. Users can select any combination of values that they want. The result is simple code and tremendous flexibility.