How to Create a Parameter Query in Access
Microsoft Access, Microsoft Office
The parameter query, while easy to set up, actually depends on a sort of quirk in the program, as we’ll see. But regardless, it’s a very powerful feature; it allows many variations on one question in a database. Ranges of dates, differences in spelling, and many others can fit a parameter query.
A parameter query on a range of dates uses the “Between—And” keywords, and has the user enter a starting and ending date as indicated. (Any criteria of this sort go in the Criteria row in Query Design view.) The quirk is, when we want the program to ask the user for info, the prompt goes between square brackets, just as if it was a field name.
This forces the parameter query to pause and say, “Huh? That’s not a field I know. Let me ask the user.” At which point it displays the prompt to the user, to inquire what the heck it is. The user answers with the appropriate data (the parameter), and voilà.
But the real power of this feature comes from combining it with the wildcard. This is the “anything-you-like” character. Though this is not universally necessary, it makes the parameter query much more flexible. If a user can only remember part of a name, a couple of letters, or a not-very-specific parameter, it’s okay. We can then use the “like” keyword to help. This says “Find something like this.” Using the wildcard character (*) lets the user enter only what they have and ask anyway. Not as much help with dates, but many of the queries we run involve text. We can put the asterisk on either side, concatenated with the prompt (using “&”). Then there’s no problem having something start with, end with, or contain the letter(s).
So if the user only knows a couple of letters in a company name, they can be entered for searching. If the result includes more than the one name, it isn’t a loss. Even if there are many results, better than not enough, or none, in most parameter query situations.
For this and other cool features, take a look at our Access classes.