Create a parameter query

Create a parameter query

1. Create a select or crosstab query.
2. In query Design view, drag the fields from the field list to the query design grid.
3. Do one or more of the following:

   Use one parameter

   In the Criteria cell for the field you want to use as a parameter, type an expression with a prompt enclosed in square brackets. For example, in a field that displays the current number of units in stock, enter the following:

   <[Number of Units in Stock:]

   Use two or more parameters

   In the Criteria cell for each field you want to use as a parameter, type an expression with prompts enclosed in square brackets.

   For example, in a field that displays dates, you can display the prompts "Type the beginning date:" and "Type the ending date:" to specify a range of values:

   Between [Type the beginning date:] And [Type the ending date:]

   Use parameters with wildcards

   In the Criteria cell for each field you want to use as a parameter, type an expression with a prompt enclosed in square brackets. To prompt the user for one or more characters to search for, and then find records that begin with or contain the characters the user specifies, create a parameter query that uses the LIKE operator and the wildcard symbol (*).

   For example, the following statement searches for words that begin with a specified letter:

   LIKE [Enter the first character to search by: ] & "*"

   The following  statement searches for words that contain the specified character:

   LIKE "*" & [Enter any character to search by: ] & "*"

4. Specify a data type for the parameters if your parameter query:

   • Is a crosstab query or the underlying query for a crosstab query. (In these cases, you must also set the ColumnHeadings property in the crosstab query.)
   • Is the underlying query for a chart.
   • Prompts for a field with the Yes/No data type.
   • Prompts for fields that come from a table in an external SQL database.

   How?

   1. On the Query menu, click Parameters.

   2. In the first Parameter cell, type the first prompt you entered in the query design grid.

   3. In the Data Type cell to the right, click the appropriate data type according to the following guidelines:

      If the parameter field data type is	Click this data type in the Data Type cell
      Currency, Date/Time, Memo, OLE Object, Text, and Yes/No	Currency, Date/Time, Memo, OLE Object, Text, and Yes/No
      Number	Byte, Single, Double, Integer, Long Integer, and Replication ID. These data types correspond to the five FieldSize property settings of the Number data type.
      Unknown	Value. This is a generic data type that accepts any type of data.
      Binary	Binary. Used with parameter queries directed to linked tables that do recognize this data type (Microsoft Access does not recognize this data type).

   4. Repeat Steps 2 and 3 for each parameter you want to specify a data type for.

   To view the results, click View on the toolbar, and then type a value for the parameter.

Create a custom dialog box to prompt for your parameter query's criteria

1. Create a custom dialog box form.

   How?

   Before you do the following procedure, create the form or report you want.

   1. Open the form or report in Design view.
   2. Double-click the form selector or report selector to open the property sheet.
   3. In the Pop Up property box, click Yes.

   4. Do one of the following:

      For a modeless pop-up form or report

      1. If you want to be able to size the form or report, in the BorderStyle property box, click Thin.
      2. Create a macro or an event procedure to open the pop-up form or report.

         Note   The Suppliers form in Northwind sample application has an example of an event procedure that displays a modeless pop-up form. Open the Northwind database in the Samples subfolder of your Office folder, and then open the Suppliers form in Design view. Click the Build button next to the OnClick property box of the Review Products button.

      For a modal pop-up form or report

      1. In the Modal property box, click Yes.
      2. In the BorderStyle property box, click Dialog.
      3. Create a macro or an event procedure to open the pop-up form or report.
   5. Attach the macro or an event procedure to a form or report by specifying the macro name or event procedure as the setting for the appropriate event property. For example, type the name of the macro or event procedure in the OnClick property box of a command button.

      To position a pop-up form or report on the screen, open it in Design view, maximize the Microsoft Access window, position the window where you want it, and then save the form or report. The pop-up window will appear in this location when the form or report is opened (assuming the AutoCenter property is set to No and you haven't specified its position in the event procedure that opens the pop-up object).

   Note   If you want to use a form as a normal form as well as a dialog box, you can use the Dialog setting of the OpenForm action to open the form temporarily as a dialog box instead of using this procedure.

2. Create or open the parameter query that will collect its criteria values from the dialog box.
3. Add to the query design grid the fields associated with each of the dialog box controls where you'll enter criteria values. 
4. For each of these fields, enter an expression in the Criteria cell that tells Microsoft Access to refer to the control on the dialog box for the criteria values. 
5. Add to the query design grid the fields whose values you want returned.

Customer Information Example

For example, you could create a dialog box form named Customer Info with a control for the CustomerID field. By entering a customer ID in the dialog box, you could have the query return the correct customer's company name, address, and city. To create this query, you'd do the following:

1. Add the CustomerID field to the query design grid. 
2. In its Criteria cell for the CustomerID field, enter the expression that tells Access to refer to the Customer ID control in the dialog box for the criteria values the query uses. In this case, you'd enter the expression Forms![Customer Info]![CustomerID].
3. Add to the query design grid the fields whose values you want returned based on the CustomerID value: CompanyName, Address, and City fields.