The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
August 2005

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 favorite pastime.

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 effortless.

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.

In this issue
  • Tip Of the Month – Copy a Query to Excel
  • From One Query Many Answers
  • Making Parameter Queries Even More Powerful
  • When to Use a Parameter Query
  • Conclusion
  • Trap of the Month – Recover from an Invisible Database Window

  • 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 field:

    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 "[Enter state]".

    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 Powerful

    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 Window

    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

    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 Excel Spreadsheet.

    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 Excel.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
    Powered by

    Custom Software | - | Westford | MA | 01886