Use parameters in queries and reports

When you want a query to ask for input every time that you run it, you can create a parameter query (parameter query: A query in which a user interactively specifies one or more criteria values. A parameter query is not a separate kind of query; rather, it extends the flexibility of a query.).

You can also create a form to collect parameter values. Using a form, you can:

  • Use data-type-specific controls
  • Enable the persistence of parameter values
  • Provide combo box controls (so that you can pick a value from a list, instead of typing it)
  • Use other controls that are available in forms

This article provides an overview of parameters, explains how to use parameters in queries, and how to use forms to enhance your use of parameters.

 Tip    Try Office 2010 In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.
Watch a video or try Office 2010.

 Note   This article contains video demos.

In this article


Overview

You can use criteria in a query in Access to restrict the set of records that the query returns. Consider the following scenario.

Suppose you have a query, named Last Order, that returns the names and addresses of your customers together with the date when you shipped their most recent order. Recently you added some exciting new products to your inventory and catalog. Every time that you ship an order, you include a copy of the current catalog. You expect that the new products will be very popular, and you want to make sure that all your customers know about them. You want to mail a new catalog to all customers who have not already received one in a shipment.

So you want to create a mailing list that contains the names and addresses of those customers whose most recent order was shipped before you produced the new catalog. To do this, you apply a criterion to the ship date field of your Last Order query. The criterion specifies that the date in the ship date field comes before the date when you started distributing the new catalog, for example, 3/31/2006. When you run the query, you get a mailing list that contains only those customers who have not received a new catalog.

But what if this situation happens again? You could modify the design of your query by changing the value specified by the criterion, but wouldn't it be nice if your query could ask you for a date and then use that date to decide which customers to include on the mailing list? You can make your query do this by using a special kind of criterion, called a parameter.

You may find the dialog boxes that are provided by a parameter query to be insufficient for your purposes. In such cases, you can create a form that better meets your parameter collection needs. This article explains how to create a form that collects report parameters.

This article assumes that you are familiar with creating queries. At a minimum, you should be familiar with creating a select query before you continue.

For more information about how to create a select query, see the article Select data by using a query. For more information about queries in general, see the article Introduction to queries.

This article provides examples of using parameters in queries. It does not provide a comprehensive reference for specifying criteria.

For more information about how to specify criteria, see the article Examples of query criteria.

Top of Page Top of Page

Use parameters in queries

Using a parameter in a query is as easy as creating a query that uses criteria. You can design a query to prompt you for one piece of information, such as a part number, or for more than one piece of information, such as two dates. For each parameter, a parameter query displays a separate dialog box that prompts you for a value for that parameter.

Add a parameter to a query

  1. Create a select query, and then open the query in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. In the Criteria row of a field for which you want a parameter applied, type the text that you want the parameter dialog box to display, enclosed in square brackets, for example:

[Start Date]

When you run the parameter query, the prompt appears in a dialog box without the square brackets.

You can also use an expression with your parameter prompts, for example:

Between [Start Date] And [End Date]

 Note   A separate dialog box appears for each parameter prompt. In the second example, two dialog boxes appear: one for Start Date and one for End Date.

  1. Repeat step 2 for each field that you want to add parameters to.


Show me Parameter query demo

                          Video created by Office Online staff writers

Assistance Problems watching the video? Try our troubleshooting tips.


You can use the preceding steps to add a parameter to any one of the following types of queries:

  • Select
  • Crosstab
  • Append
  • Make-table
  • Update

You can also add a parameter to a union query.

  1. Open the union query in SQL view.
  2. Add a WHERE clause that contains each of the fields for which you want to prompt for a parameter.

If a WHERE clause already exists, check to see whether the fields for which you want to use a parameter prompt are already in the clause, and if not, add them.

  1. Instead of using a criterion in the WHERE clause, use a parameter prompt.

Matching part of a field value with a parameter string

You may want a little variability in the way your query applies a parameter. For example, you might want a query to accept a text string and match that to any part of a field. You can do this by using the Like keyword in combination with wildcard characters. For example, you might want your query to prompt for a country/region of origin, but to match whenever the relevant field value contains the parameter string. To do this:

  1. Create a select query, and then open the query in Design view.
  2. In the Criteria row of the field for which you want the parameter applied, type Like "*"&[, type the text that you want to use as a prompt, and then type ]&"*".

When you run the parameter query, the prompt appears in the dialog box without the square brackets, and without the Like keyword or wildcard characters.

  1. When the query accepts the parameter, it matches on values that contain the parameter string. For example, the parameter string us matches rows where the parameter field has a value of Australia and rows where the value is USA.

You can also use the Like keyword and wildcard characters to specify that a parameter should match the beginning or ending of a field value. To match the beginning of a field value, omit the quotation marks, the wildcard character, and the ampersand (&) that precede the opening square bracket. To match the ending of a field value, omit the ampersand, the quotation marks, and the wildcard character that follow the closing square bracket.

Matching based on unequal values

You might want to collect a parameter, but instead of having the query return rows with a matching value, you want the query to return rows for which some other comparison evaluates as true. For example, you might want to prompt for a year and then return rows where the value of year is greater than the parameter string. To do this, type a comparison operator to the left of the first square bracket that encloses the parameter prompt, for example, >[Year:].

Top of Page Top of Page

Specify parameter data types

You can also specify what type of data a parameter should accept. You can specify the data type for any parameter, but it is especially important to specify the data type for numeric, currency, or date/time data. When you specify the data type that a parameter should accept, users see a more helpful error message if they enter the wrong type of data, such as entering text when currency is expected.

 Note   If a parameter is configured to accept text data, any input is interpreted as text, and no error message is displayed.

To specify the data type for parameters in a query, follow these steps:

  1. With the query open in Design view, on the Design tab, in the Show/Hide group, click Parameters.
  2. In the Query Parameters dialog box, in the Parameter column, type the prompt for each parameter for which you want to specify the data type. Make sure that each parameter matches the prompt that you use in the Criteria row of the query design grid.
  3. In the Data Type column, select the data type for each parameter.


Show me Parameter form demo

                          Video created by Office Online staff writers

Assistance Problems watching the video? Try our troubleshooting tips.


Top of Page Top of Page

Create a form that collects parameters

Although parameter queries feature a built-in dialog box that collects parameters, they provide only basic functionality. By using a form to collect parameters, you gain the following features:

  • The ability to use data-type-specific controls, such as calendar controls for dates.
  • Persistence of the collected parameters, so that you can use them with more than one query.
  • The ability to provide a combo box or list box for parameter collection, which lets you pick from a list of available data values.
  • The ability to provide controls for other functions, such as opening or refreshing a query.

Watch the video to get an idea how it works.



Show me Parameter form demo

                          Video created by Office Online staff writers

Assistance Problems watching the video? Try our troubleshooting tips.


Follow these steps to create a form that collects parameters.



Step 1: Create a form that accepts input

  1. On the Create tab, in the Forms group, click Form Design.
  1. In Design view, press F4 to display the property sheet and then specify the form properties, as shown in the following table.
Property Setting
Caption Enter the name that you want to appear in the title bar of the form.
Default View Single Form
Allow Form View Yes
Allow Datasheet View No
Allow PivotTable View No
Allow PivotChart View No
Scroll Bars Neither
Record Selectors No
Navigation Buttons No
Border Style Dialog
  1. For each parameter that you want the form to collect, click Text Box in the Controls group on the Design tab.
  2. Set the properties for the text boxes, as shown in the following table.
Property Setting
Name Enter a name that describes the parameter, for example, StartDate.
Format Choose a format that reflects the data type of the parameter field. For example, select General Date for a date field.
  1. Save the form and give it a name, such as Date Range.

Step 2: Create a module to check whether the parameter form is already loaded

  1. On the Create tab, in the Other group, click Module. If this command is unavailable, click the arrow beneath either the Macro or the Class Module button, and then click Module.

A new module opens in the Visual Basic Editor.

  1. Type or paste the following code into the Visual Basic Editor:
Function IsLoaded(ByVal strFormName As String) As Boolean

Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)

If oAccessObject.IsLoaded Then
  If oAccessObject.CurrentView <> acCurViewDesign Then
    IsLoaded = True
  End If
End If

End Function
  1. Save the module, and then close the Visual Basic Editor.

Step 3: Create a macro group that controls the form

  1. On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.
  1. On the Design tab, in the Show/Hide group, click Show All Actions.
  1. On the Design tab, in the Show/Hide group, click Macro Names to display the Macro Name column.
  1. Type a macro name, such as Open Dialog, in the Macro Name column.
  2. Click the cell in the Action column next to the new macro name, click the drop-down arrow, and then click OpenForm.
  3. In the Action Arguments grid (located below the macro design grid), set the action arguments (action argument: Additional information required by some macro actions. For example, the object affected by the action or special conditions under which the action is carried out.), as shown in the following table.
Argument Setting
Form Name Enter the name that you gave your report parameter form.
View Form
Data Mode Edit
Window Mode Dialog
  1. In the next Action cell, click CancelEvent to add a second action. This action will be used to cancel previewing or printing the report when a user clicks the Cancel button on the form.
  2. If the Condition column is not displayed, click Conditions in the Show/Hide group on the Design tab.
  3. In the Condition column, type Not IsLoaded, followed by an opening parenthesis and a quotation mark, followed by the name that you gave your report parameter form, and then followed by a quotation mark and a closing parenthesis. For example, if your form is named Parameter Collector, you type Not IsLoaded("Parameter Collector").
  4. In the next blank row of the macro design grid, type a new macro name, such as Close Dialog.
  5. In the adjacent Action cell, click the drop-down arrow, and then click Close. This action will be used by the query to close the parameter form. Set its action arguments, as shown in the following table.
Argument Setting
Object Type Form
Object Name Enter the name that you gave your report parameter form.
Save No
  1. In the next blank row of the macro design grid, type a new macro name, such as OK. In the adjacent Action cell, click the drop-down arrow, and then click SetValue. This action will be used to hide the report parameter form when the user clicks OK. Set its action arguments, as shown in the following table.
Argument Setting
Item [Visible]
Expression No
  1. In the next blank row of the macro design grid, type a new macro name, such as Cancel. In the adjacent Action cell, click the drop-down arrow, and then click Close. This action will be used to close the report parameter form when the user clicks Cancel. Set its action arguments, as shown in the following table.
Argument Setting
Object Type Form
Object Name Enter the name that you gave your report parameter form.
Save No
  1. Save and close the macro group. Give the macro group a name, for example, Date Range Macro.

Step 4: Add OK and Cancel command buttons to the form

  1. Reopen the Date Range form in Design view.
  2. Ensure that Use Control Wizards in the Controls group on the Design tab is not selected.
  3. On the Design tab, in the Controls group, click Button.
  1. Position the pointer below the text boxes on your form, and then drag to create an OK command button.
  2. If the property sheet is not visible, press F4 to display it.
  3. Set the OK button's properties, as shown in the following table.
Property Setting
Name OK
Caption OK
Default Yes
OnClick Enter the name of the macro, for example, Date Range.OK.
  1. Create a Cancel command button and set its properties, as shown in the following table.
Property Setting
Name Cancel
Caption Cancel
OnClick Enter the name of the macro, for example, Date Range.Cancel.
  1. Save and close the form.

Step 5: Use the form data as query criteria

  1. Open the query in Design view.
  2. Enter the criteria for the data. Use the Forms object, the name of the form, and the name of the control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.):
    • For example, in an Access database (.accdb or .mdb), for a form named Date Range, you use the following expression to refer to controls named Start Date and End Date in the query:

Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate]

Top of Page Top of Page

 
 
Applies to:
Access 2007