Parameter Queries – Why Make One Cookie
If You Can Make A Batch?
There's nothing like a home-made cookie. When they come out of
the oven they're warm and oozy and they taste just great. I used to
make cookies with my sons. They're teenagers now so they’ve lost
interest in baking cookies with dad, but back when they were
smaller, whipping up a batch of chocolate chip cookies was a
I like my cookies pretty big. Rob (my youngest son) likes them
small, while Jason likes them in between. Since we were making a
batch and not just one cookie, making each one slightly different
was simple. Even though there was variation in the size (and perhaps
shape) of the cookies, they were all chocolate chip cookies, and
creating custom cookies during the baking process was virtually
If only shaping and forming data were so easy! The typical Access
user runs multiple distinct queries in a database to create data
sets that may only differ by one variable. For example, let’s say
that you maintain an Access database for a car mechanic who wants to
send reminders to his clients when it’s time for an oil change. You
may create a separate query to retrieve all customers who were last
serviced in January, and then repeat the process for February,
March, and so on. The big news this month is that it may not always
be necessary to write a separate and distinct query to create a data
set for each month in the case of this scenario. In some cases you
may be able to write a single query with an interface that prompts
you for parameters and returns the results based on your input. This
type of query is called a Parameter Query, and it's the subject of
this month's newsletter.
|From One Query Many Answers|
Let's say that you have data in the following table:
This is a list of customer first name, last name, city and
state. Let's say that we're interested in getting list of all
of the customers in New Hampshire. To extract that
information, we'd create a query as follows, specifying the
abbreviation for New Hampshire (NH) as criteria in the State
Running this query would return two records for the two
customers in New Hampshire.
Now let's say that you would also like to create a separate
data set for customers in Massachusetts, Texas and Arizona.
Rather than create a separate query for each state, we can
create one query that will return these results as needed,
based on criteria input by the user.
We'd set this up as follows:
Notice that in the criteria row under state we've put
When you run this query you get the following prompt
(notice that the text above the input box matches the text
typed between the brackets in the query form):
If you enter MA you get the following:
As you can see, the resulting set shows the six customers
who live in Massachusetts.
The notation for a Parameter Query is simply text within
brackets in the criteria field for the variable you’d like to
input in the prompt box. The text you enter between the
brackets shows up in the box, above the input field. In this
Parameter Query, Access takes the criteria [Enter state],
replaces it with your input ("MA"), and executes the query.
This is a very powerful feature that can turn a single
dedicated query into a much more flexible query that can run
“on-the-fly” with dynamic criteria.
|Making Parameter Queries Even More
Parameter Queries become even more flexible when you use
wildcards. Let’s imagine that you wanted to create a data set
of every customer who lived in a state starting with the
letter N. You could enter the criteria Like [Enter state's
First Letter] & "*" under the state column. When the
query runs, you'll get the prompt Enter state’s First Letter.
In this instance the asterisk will act as a wildcard and
you'll get every state that begins with whatever letter you
type in. Considering the table in the above example, typing
“N” would create a data set of customers in NY and NH. Note
that wildcards are very powerful in and of themselves and
we'll be reviewing those in a future issue.
You may utilize Parameter Queries for virtually any type of
query based on criteria. For example, if you’re interested in
counting the number of states in which you have customers, you
could use a Group By query (See "Group By" in Help for details
on this) to get a count of states “on the fly”. You can also
put Parameter Queries into delete and update queries.
It's also possible to designate more than one field for
parameters. Let's say there were many more records and you
were interested in getting back not only the customers in a
particular state, but also customers with a last name that
starts with a certain letter. In this case you would have not
only the criteria [Enter state] under the state column
in the query design, but also [Enter first letter of
Customer Last Name] & "*" in the Last Name column.
When you run this type of query you'll get two prompts, one
for the State and one for the first letter of the Customer
Last Name. I know this particular example is not particularly
realistic, but I'm sure you understand how multiple parameters
can make a query even more flexible.
|When to Use a Parameter Query|
Parameter Queries are best employed any time when you want
a subset of data from a data set but don't know beforehand
what that subset will be. Think of them as a query “on the
fly”, offering flexibility without the hassle of creating a
brand new query.
The most typical uses are for ad hoc questions, or the as
the source for reports.
When you use a parameter query as a source for a report,
the query (or SQL statement) that provides the data to the
report should include a parameter in the query. Then when the
report is run, you'll get the prompt and the report will
return the records consistent with the entry you make in the
parameter dialog box.
Although less common, parameter queries can also be used as
the data source for forms. When using these queries in forms
and reports, keep in mind that the query will run before the
form or report is open. Once the query runs, the form and
report will act as it does with any other query or data set.
If you find that you create a number of queries that do
essentially the same thing with changing query criteria,
consider using a Parameter Query instead. You'll get a much
bigger bang for your buck and you'll have a more flexible tool
to work with.
|Trap of the Month – Recover from an Invisible Database
Does your database window ever disappear? Most likely,
you’ve just inadvertently moved it off the screen. The easiest
way to get it back is to choose Windows | Cascade from the
menu at the top of the screen. This will show all the active
windows in your application, including the database window.
Once you see it, you can maximize it and you'll be all set.
Tip Of the Month – Copy a Query to
Excel does a magnificent job of pushing numbers around –
it's much easier to use for calculations than Access. It’s
easy to take the results from an Access query and move them to
Excel for further manipulation if you know a couple of tricks.
1) Start with a blank Excel Spreadsheet.
2) If the query you want to copy already exists, open
Access, click on the query tab, then choose Edit | Copy from
the menu at the top of the screen. Go to Excel, choose edit
paste and you'll see the results of query pasted into the
If you have the query results in front of you in Access,
you may also do the following:
In the top left corner of the results grid there is a
gray square. Click this square and it will highlight all the
rows of the query results. Choose Edit | Copy from the menu at
the top of the screen and then paste the results into