Creating Access Parameter Queries to Specify Criteria Dynamically

In the previous lesson, you built a query in Design view that hard-coded the criterion TX for the State field. That approach works perfectly if you only ever want to see Texas clients, but it does not scale. Every time you wanted to look at clients in a different state, you would have to open the query in Design view, replace TX with the new abbreviation, save the query, and then switch to Datasheet view to see the results. For a database designer that round-trip is tolerable; for an end user it is unworkable, and exposing Design view to a non-technical user is generally something you want to avoid in the first place.

A parameter query solves this problem cleanly. Instead of writing a literal value on the Criteria row, you write a placeholder enclosed in square brackets. When Access opens the query, it sees the placeholder, recognizes that it is not a field name in any of the underlying tables, and treats it as a prompt — so it pops up a small dialog box asking the user to supply the value at runtime. The query then executes against whatever the user typed, returning matching records exactly as if the value had been hard-coded all along.

Parameter queries are flexible, but they have a few constraints worth knowing about up front. You can use parameters anywhere a criterion or a calculated expression goes, and you can use them with any data type — text, numbers, dates, currency, or yes/no. What you cannot do is use a parameter to specify a sort order, or to substitute the name of a field, or to substitute the name of a table. Parameters supply values, not identifiers. If you need dynamic field names or sort orders, you will have to build the SQL string at runtime in VBA and then assign it to the query.

To create a parameter query that asks for a state each time it runs, start by building an ordinary query against the Clients table. Drag the fields you want into the design grid — CompanyName, City, State — exactly as you would for any other query. Then, on the Criteria row of the State column, instead of typing TX, type the following:

[Enter State:]

The text inside the brackets is the prompt that Access will display to the user. You can write anything you like there — [Which state?], [State abbreviation], [Enter the two-letter state code] — as long as the text inside the brackets does not exactly match the name of a field in any of the tables the query draws from. If it does match a field name, Access will silently treat your placeholder as a reference to that field instead of as a prompt, and the query will return results that look wrong without an obvious explanation.

Switch the query to Datasheet view by clicking the View button, and Access displays a small dialog box with the prompt text and a single text box. Type a state code, click OK, and Access substitutes whatever you typed in for the placeholder before running the query. In addition to the standard comparison operators (<, >, =, <=, >=), you can combine a parameter with some advanced operators for partial matches and ranges, which we will cover in the next section.

If you switch the query to SQL view, you will see that Access has generated a PARAMETERS clause at the top of the statement that declares the prompt as a named parameter, followed by the usual SELECT and WHERE. The clause looks like this:

PARAMETERS [Enter State:] Text(255);
SELECT CompanyName, City, State
FROM Clients
WHERE State = [Enter State:];

You can edit this SQL directly if you prefer, and many developers do once they are comfortable with the syntax — it is faster than dragging things around in Design view.

By default, Access treats every parameter you write into a Criteria row as Text. That is fine when the field you are comparing against is itself Text, like State or City. It is less fine when the field is a Number, a Currency value, or a Date — Access will sometimes coerce the input correctly, and sometimes it will silently fail to match anything, or throw a data-type-mismatch error that gives no hint about which parameter is the offender.

The fix is to declare the parameter's data type explicitly. With the query open in Design view, go to the Query Design ribbon and click Parameters. Access opens a small dialog with two columns: Parameter and Data Type. Type the bracketed prompt text exactly as it appears in your Criteria row into the Parameter column — including the brackets — and pick the matching data type in the second column. For a date prompt you would use Date/Time; for a numeric prompt, Long Integer or Double depending on the range. Now Access will treat the user's input as the correct type before comparing it to the field, and the query will behave the way you expect.

Declaring types also makes the query callable from VBA and from macros with the correct argument types, which matters when you start passing parameters programmatically rather than relying on the popup dialog.

Along with the standard comparison operators (<, >, =, <=, >=), Access supports two keyword operators that combine especially well with parameter queries: Like and Between. These give you partial matching and range matching, which are difficult to express with the standard operators alone.

• Using the Like operator
  
  The Like operator searches for partial matches against a pattern, where = only matches when the entire field equals the value exactly. With State that distinction barely matters — the field holds a two-letter code, so typing the whole thing is no hardship. With City the difference is significant. If a user remembers only that the city starts with an S, an =[Enter City] criterion forces them to type the entire name correctly, and a typo returns zero rows.
  
  To allow partial matches, change the criterion to:
  
  

  Like [Enter City] & "*"

  
  The asterisk (*) is Access's wildcard for "any sequence of characters, including none." The ampersand (&) is the string concatenation operator — it glues the user's input onto the wildcard, so typing S becomes the pattern S*, which matches Seattle, San Francisco, Santa Fe, and any other city beginning with S. You can place wildcards on either or both sides depending on how the user might remember the value: Like "*" & [Enter City] & "*" matches the input as a substring anywhere within the field, which is useful for full-text-style searches but slower on large tables because Access cannot use an index on a leading wildcard.
  
  Access also supports the ? wildcard for a single character and the # wildcard for a single digit, which are occasionally useful when the user knows the length and shape of the value they are looking for — a product code with a known format, for example.
  

  

• Using the Between operator
  
  The Between operator returns records whose values fall between two endpoints, inclusive of both. It works with numbers, dates, currency, and even text (where comparisons are alphabetical). To find Hours records whose DateWorked falls within a date range chosen at runtime, set the Criteria row of the DateWorked column to:
  
  

  Between [Enter Start] And [Enter End]

  
  When the query runs, Access prompts twice — once for the start value, once for the end — and returns records whose DateWorked is greater than or equal to the start and less than or equal to the end. Entering 6/1/2009 and 6/15/2009 returns every record from June 1 through June 15, including both endpoints.
  
  The same pattern works for numeric ranges (Between [Min Price] And [Max Price]) and is far more readable than the equivalent expression with >= and <= joined by And. Remember to declare both parameters in the Query Parameters dialog with the matching data type, or Access may compare a typed string to a date field and quietly return nothing.
  

  

Reports bound to parameter queries are a common pattern. You might want to show customers who have not paid within a window of days, where the window is supplied at runtime; you might want a report scoped to one customer, or to sales for a single region. If the report's record source is a parameter query and you simply open the report, Access will prompt the user with the same dialog box the query would have used directly. That is functional, but it is rarely what you want in a polished application, because the prompt is a bare text box with no validation and no list of valid values.

A better pattern is to drive the parameter from a form control — a combo box, list box, or text box — and pass its value to the report when the report opens. The Macro Designer supports this directly: the OpenReport action takes a Parameters argument where you map each named parameter in the query to an expression, and that expression can reference any control on any open form using =[ControlName] or, when you need to be explicit about which form, =[Forms]![frmName]![ControlName].

In the example below, the report rptChapters is bound to a parameter query that expects a parameter named SelectedAuthor. The macro pulls the value from a combo box named cboAuthors on the form that hosts the macro's button:

OpenReport
Report Name: rptChapters
View: Report
Window Mode: Normal
Parameters
SelectedAuthor: =[cboAuthors]

This opens the report in its own window. If instead you want the report to appear inside a subform control on an existing form — a common arrangement for dashboard-style interfaces — use the BrowseTo action. The path to the subform control depends on whether the host form is currently displayed standalone or hosted inside an Access navigation control, and an invalid path raises an error. The macro below tries the standalone path first and falls back to the navigation path if that fails, using OnError to continue past the first attempt rather than halting:

OnError
Go to: Next
/* Try to load the report in the host form (frmAuthorsParameters) */
BrowseTo
Object Type: Report
Object Name: rptChapters
Path to Subform Control: frmAuthorsParameters.sfrmChild
Data Mode: Edit
Parameters
SelectedAuthor: =[cboAuthor]
/* If this fails, try to load it in the navigation subform */
BrowseTo
Object Type: Report
Object Name: rptChapters
Path to Subform Control: frmMain.sfrmNav>frmAuthorsParameters.sfrmChild
Data Mode: Edit
Parameters
SelectedAuthor: =[cboAuthor]

The next section walks through building a basic parameter query against the Clients table.

Try this on your own in Access. The exercise cannot be graded automatically, but you can post your results in the discussion area.

1. Open the query that displays all company names and the states they are located in. On the Criteria row of the State field, type [Enter State:], then click the View button to run the query.
2. Access displays the parameter prompt you defined. Type TX, then click OK.
3. The query returns the companies located in Texas. Run it again with a different state code to confirm the parameter is being read each time the query executes.

Three issues account for the majority of parameter-query problems:

• Prompt text matches a field name. If [State] is the bracketed text on the Criteria row, Access interprets it as a reference to the State field rather than as a prompt, and the query effectively compares the field to itself. Always pick prompt text that cannot be confused with a real field name — [Enter State:] is safe; [State] is not.
• Data type mismatch. If the field is a Date or a Number but the parameter has not been declared in the Query Parameters dialog, Access may compare typed strings to typed values and return zero rows without an error. Declare the type explicitly.
• Empty input. If the user clicks OK without typing anything, the parameter is Null, and any comparison with Null returns Null — which is treated as False in a WHERE clause, so the query returns nothing. To match all records when the user enters no value, use Like [Enter State:] & "*" on a text field, or wrap the comparison in Nz() for numeric fields.