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.

  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

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

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

Problems watching the video? Try our troubleshooting tips.

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

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

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

• Step 3: Create a macro group that controls the form

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

• Step 5: Use the form data as query criteria

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.

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

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

  2. On the Design tab, in the Show/Hide group, click Show All Actions.

  3. On the Design tab, in the Show/Hide group, click Macro Names to display the Macro Name column.

  4. Type a macro name, such as Open Dialog, in the Macro Name column.

  5. Click the cell in the Action column next to the new macro name, click the drop-down arrow, and then click OpenForm.

  6. In the Action Arguments grid (located below the macro design grid), set the action arguments, as shown in the following table.

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

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

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

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

  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.

• 1. Create a Cancel command button and set its properties, as shown in the following table.

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

     • 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]