Use a parameter to make a query ask for input

This [technique] will save us HOURS
each week and each month
in running our multiple reports.
~ Access customer, via Office.com feedback

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

 Note   You cannot use a form to collect query parameters in a web database.

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.

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

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

Add a parameter to a union query

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.

Top of Page Top of Page

Create a form that collects parameters

 Note   You cannot use a form to collect query parameters in a web database.

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.

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.
  2. 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, on the Design tab, in the Controls group, click the Text Box button.
  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 Macros & Code group, 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 macros to control the form

  1. On the Create tab, in the Macros & Code group, click Macro.
  2. On the Design tab, in the Show/Hide group, click Show All Actions.
  3. Right-click the macro object tab, and then click Save.
  4. Type a name, such as Open Dialog, in the Macro Name box, and then click OK.
  5. Click the drop-down arrow next to Add New Action, and then click OpenForm. You will use this action to open the parameter form that you created earlier.

The OpenForm action appears and displays a set of arguments.

  1. Set the action arguments as shown in the following table.
Argument Setting
Form Name Enter the name that you gave your parameter form.
View Form
Data Mode Edit
Window Mode Dialog
  1. Click the drop-down arrow next to Add New Action, and then click If.
  2. In the If action, in the box next to If, type Not IsLoaded, followed by the name that you gave your report parameter form, enclosed in quote marks and parentheses. For example, if your form is named Date Range, you type Not IsLoaded("Date Range").
  3. In the If action, click the drop-down arrow next to Add New Action, and then click CancelEvent.
  4. Save and close the macro.
  5. Repeat steps 1 through 3 to create a new macro. Give the new macro a name, such as Close Dialog.
  6. Click the drop-down arrow next to Add New Action, and then click CloseWindow. This action will be used by the query to close the parameter form.
  7. Set the action arguments as shown in the following table.
Argument Setting
Object Type Form
Object Name Enter the name that you gave your parameter form.
Save No
  1. Save and close the macro.
  2. Repeat steps 1 through 3 to create another new macro. Give the new macro a name, such as OK.
  3. Click the drop-down arrow next to Add New Action, and then click SetValue. This action will be used to hide the report parameter form when the user clicks OK.
  4. Set the action arguments as shown in the following table.
Argument Setting
Item [Visible]
Expression No
  1. Repeat steps 1 through 3 to create another new macro. Give the new macro a name, such as Cancel.
  2. Click the drop-down arrow next to Add New Action, and then click CloseWindow. This action will be used to close the parameter form when the user clicks Cancel.
  3. Set the action arguments as shown in the following table.
Argument Setting
Object Type Form
Object Name Enter the name that you gave your parameter form.
Save No

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

  1. Reopen the parameter form in Design view.
  2. Make sure 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.
  4. Position the pointer below the text boxes on your form, and then drag to create an OK command button.
  5. If the property sheet is not visible, press F4 to display it.
  6. 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 2010