Use parameters in queries, forms, and reports

When you want a query in Access to ask for input every time that you run it, you can create a parameter query.

You can also create a form to collect parameter values that will be used to restrict the records returned for queries, forms or reports. This article explains how to use forms to enhance your use of parameters in queries, forms, and reports.

In this article

Overview

Use parameters in queries

Specify parameter data types

Create a form that collects parameters

Create a form that collects parameters for a report

Overview

You can use criteria in a parameter query in Access to restrict the set of records that the query returns. 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 query and report parameters. This article assumes that you are familiar with creating queries and defining parameters in queries. At a minimum, you should be familiar with creating a select query before you continue.

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 create a select query, see the article Create a simple select query.

• For more information about queries in general, see the article Introduction to queries.

• For more information defining parameters in queries, see the article Use parameters to ask for input when running a query.

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

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.

3. 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, or Update.

You can also add a parameter to a union query by following these steps:

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.

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

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.

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.

The following video shows how you can create a simple form to collect parameters for a query instead of using the dialog boxes normally associated with parameter queries.

Create a form that collects parameters for a report

There are several ways you could approach this scenario, but we'll show just one technique using mostly macros. Follow these steps to create a form that collects parameters for a report.

Step 1: Create a form that accepts input

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

Step 3: Create a macro that controls the form and report

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

Step 5: Use the form data as query criteria

Step 6: Add the macro actions to the report events

Step 7: Try it out

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

3. For each parameter that you want the form to collect, click Text Box in the Controls group on the Design tab.

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

5. Save the form and give it a name, such as frmCriteria.

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

1. On the Create tab in the Macros & Code group, click Module. Note, if you're using Access 2007, on the Create tab, in the Other group, click Module.

   A new module opens in the Visual Basic Editor.

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

3. Save the module with a unique name, and then close the Visual Basic Editor.

Step 3: Create a macro that controls the form and report

Using the submacro features of Access macros we can define all the needed steps we need to make in a single macro. We'll create four submacros - Open Dialog, Close Dialog, OK, and Cancel - to control the various tasks needed for this procedure. Using the screenshot below as a guide, create a new macro with the following submacros and actions. Note, for this example, our parameter form is called frmCriteria. Adjust your macro to match the name of the form you created earlier. You'll also need to be sure to click Show All Actions on the Design tab in order to view all macro actions.

Save and close the macro. Give the macro a name, for example, Date Range Macro.

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

1. Reopen the parameter form you created earlier 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.

   Property	Setting
   Name	OK
   Caption	OK
   Default	Yes
   OnClick	Enter the name of the macro, for example, Date Range Macro.OK.

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

8. Save and close the form.

Step 5: Use the form data as query criteria

1. Open the query you created earlier 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 frmCriteria, you use the following expression to refer to controls named Start Date and End Date in the query:

     Between [Forms]![frmCriteria]![Start Date] And [Forms]![frmCriteria]![End Date]

Step 6: Add the macro actions to the report events

1. Open the report that you're wanting to use in Design view.

2. If the property sheet is not visible, press F4 to display it.

3. Make sure the Record Source property of the report is using the parameter query you defined earlier.

4. Set these two additional report properties, as shown in the following table.

   Property	Setting
   OnOpen	Enter the name of the macro, for example, Date Range Macro.Open Dialog.
   OnClose	Enter the name of the macro, for example, Date Range Macro.Close Dialog.

   In the Open event of the report, Access will run the actions defined in the Open Dialog submacro of the Date Range Macro object. Similarly, when you close the report, Access will run the actions defined in the Close Dialog submacro of the Date Range Macro object.

5. Save and close the report.

Step 7: Try it out

Now that you're created all of the Access objects, it's time to try it out. Open your report in Report View or Print Preview and notice that before Access displays the report, your parameter form opens in dialog mode. Enter the criteria needed into the text boxes you created previously and then click the OK command button on the form. Access then hides the form (Visible = No) and opens the report with only data that matches your criteria. This works because the parameter query that the report is based on can read the values in the controls on the hidden form. When you close the report, Access will also close the parameter form.

Top of Page