Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Parameter Queries

Parameters simplify query creation by allowing single query to produce different results depending on values you enter when you view the query. This lesson is intended to introduce the theory of parameters and the basics of using them in queries. The examples we are using are not forms driven. In a ‘real’ application all user interaction with the data is done through forms and the use of parameters is much more streamlined than the following examples might lead you to think.

image

In this query definition, the year, 2011, is entered as a literal value in the calculation of the review date. The only thing this query can calculate is a review date in 2011. Without the use of parameters, every year we would have to create a new query or re-design this one in order to calculate a new set of review dates.

Now, let’s look at a similar query, this time with a parameter in the calculation instead of a literal year value. Only one small modification is required. In place of 2011 for  the imagefirst argument of the DateSerial function, we put in [year]. Parameters are always enclosed in square brackets. Whatever text you type between the brackets will be used to prompt the user when the query is run. This text should not be the same as any of the field names in the query’s data source.

When you run the query, Access displays an input box with the text you placed between the square brackets as its prompt:

image

 

If you type 2011 in the input box and press enter or click OK, Access will substitute that value for the parameter in the calculation and produce a list of review dates in the year 2011.

When it is time for 2012 reviews, you can run the query again, this time entering 2012 into the input box instead of 2011.

 

Parameters are most frequently used to specify query criteria. Here’s an example that uses a parameter to specify the month in which increases come into effect. So, when

image

the query runs, an input box requesting the month appears. The payroll department could use a query like this to determine which employees to update on the first run of any given month. The last column of the grid handles cases when the user does not enter a month and causes the full list to be displayed.

image

The Parameters Dialogue

While most parameter queries do not require its use, the parameters dialogue serves two important purposes. First it lets you control the order in which the parameters are requested. Second it lets you specify a data type for the value to be entered.

imageIt is not always possible to clearly indicate the data type that you expect the user to enter. For example, the month value in the example queries in this article is a number. That is because the effective month calculation produces a number between 1 and 12. By specifying a byte type, you are ensuring that the user will receive a more meaningful error message if they type a month name than if you did not use the parameters dialogue to explicitly state that a numeric value is expected. To open the parameters dialogue, right-click in the top portion of the query designer and select Parameters.

If the query refers to the same parameter more than once, it is important that the references have identical spelling. Otherwise, Access will treat the variations as additional parameters.

Comments are closed.