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 DateOfBRith, 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?
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
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.
|
|