Parameter Queries in Access
If you’re already using MS Access, you know about the query builder. You can
use it to create queries without any knowledge of SQL, and you can use it to
help you learn SQL by viewing your competed queries in SQL view. See the
subqueries article in the June 2002
newsletter.
You may have wondered how to ‘generalize’ your queries so that you can run
the same query to get different results each time. For instance, how can I
create a query to return employee reasons for absences one name at a time? I
don’t want to have to write one query for Smith, one for Jones, one for
Callahan, etc. The answer is to write a parameter query.
A parameter query is an outgrowth of an error message. If I write a query
that looks for DateOfB
Rith, my database is not going to guess that I mean
DateOfBirth. It’s going to come back and tell me it has no idea what I’m talking
about and that if I’m going to reference fields that it can’t find in the
database, I’m going to have to type the values in myself. It pops up an error
message that basically says “DateOfBrith?” What the heck is that?
Seems like a pain, but we can use this to our advantage. In
criteria row in the first column of the graphic below , we’ve intentionally typed in something that
looks
like a field name to Access but we know is not. We know that Access will pop up
the error message box with the alleged field name we asked for so we give a fake
field name that looks like a question—
”Enter
Last Name”.
Now when we run this query, we can enter a new name and get a different
result each time. See the graphic below.
.
If you base a report on a parameter query, you will force the query to run
each time you print out the report.
You could also use the
Like function
to return a group of last names. Instead of
[Enter Last Name] in the criteria box as above, try entering
Like [Enter Last Initial] & '*'. If the
user types ‘C’, all last names beginning with C will be returned.