Part IV: Professional Database Development
892
FIGURE 25.9The Query Parameters dialog box
Tip
If you want to add more-complex parameters, such as a range of dates, use an expression such as Between
[Enter the Start Date] and [Enter the End Date] as a criteria in a date field. This would display two separate
parameter dialog boxes and then filter the date value appropriately.
Unfortunately, Access parameter queries don’t provide a way to supply default values for parame-
ters. Your best bet is to always include the asterisk in your criteria expression so that, if the user
closes the parameter dialog box without entering a value, the query will return all record because
the criteria expression will resolve to Like “*”.Caution
You can only use Like “*” with text fields. For numeric fields, you can set the criteria to [My Prompt] OR ([My
Prompt] IS NULL). Just be certain that both occurrences of My Prompt are typed identically. (Copy-and-paste
is a good idea.)
Creating an interactive filter dialog box
The problem with parameter queries is that they’re only suitable for simple parameters. The users
have to know exactly what to type into the parameter dialog box, and if they enter the parameter
incorrectly, they won’t see the results they expect. Also, using parameter queries for entering com-
plex criteria is fairly difficult.A better technique is to create a simple form, place controls on the form, and reference the controls
from a query as parameters. In other words, the query uses the form’s controls to get its parameter
values. This is a huge advantage to the users because the controls can help the user select the crite-
ria by presenting lists or drop-down menus of the acceptable parameter values. Plus, code can be
added to each control’s AfterUpdate event to validate the user’s input to ensure that the query