Create and use a parameter query (ADP)

1. In the Database window, under Objects, click Queries , click the stored procedure or inline user-defined function you want to open, and then click Design on the database window toolbar.
2. Do one or more of the following:
   • In the Criteria cell for each column you want to prompt a value for, type a named parameter with no embedded spaces preceded by an at sign (@) as the following example shows:

     @Enter_Customer

   • In the Column cell, enter a named parameter in a computed column expression as the following example shows:

     Price * @Enter_Discount

3. To change the data type of the parameter, right-click in the Design grid of the Query Designer, select Properties from the shortcut menu, and then click the Stored Procedure Parameters or Function Parameters tab.

   Change the data type when you want to do a calculation and you must ensure that the parameter is a number data type, such as int or money. If you type a text data type, use the short form to specify the text length, for example nvarchar(50) or char(100).

4. To change the parameter default, right-click in the Query Designer, select Properties from the shortcut menu, and then click the Stored Procedure Parameters or Function Parameters tab. The value you enter will be entered as the default value. For example, if you enter a parameter default value of 'CA', when you run a parameter query with the following SQL statement:

   SELECT * from authors WHERE state = @state

   The parameter query will enter CA as the parameter default value and not prompt you for a parameter.

5. Click Save to save the stored procedure or inline user-defined function.

6. Run the parameter query to verify that it works as intended.

   How?

   1. Do one of the following:
      • In the Database window, click Queries under Objects, click the parameter query you want to run, and then click Open on the database window toolbar.
      • In the Query Designer, Click Run to run the parameter query.

      Access shows the Enter Parameter Value dialog box for each parameter in the query.

   2. Do one of the following for each parameter value you are prompted for:
      • To enter a parameter value, type the value.
      • To enter the default value, select <DEFAULT>.
      • To enter a Null value, select <NULL>.
      • To enter a zero-length or blank string, leave the box blank.

   Note  You cannot run or create a view as a parameter query.

When you use a stored procedure or inline user-defined function parameter query as the record source of a form or report, you can do one of two things:

• Pass the value of the parameter to a field control on the form or report
• Prompt for the value of the parameter in the Enter Parameter Value dialog box.

To do this, you use the InputParameters property on the form or report, which maps the named parameters in the stored procedure or inline user-defined function to expressions that reference the field control in the form or report.

For example if you have a parameter query that uses the following named parameters:

SELECT State, SalesYear from Orders WHERE Orders.State = @EnterState AND Orders.SalesYear =@EnterSalesYear

You can set the InputParameters property as follows:

@State char=[Form]![StateList], @SalesYear smallint=[Enter year of interest]

For the first parameter, Access sets the @State parameter to be the current value of the StateList control. For the second parameter, Access prompts the user for the @SalesYear parameter. If there were any other parameters to the stored procedure or inline user-defined function that were not in this list, they would have default values assigned.

1. In the Database window, under Objects, click Queries , click the form or report you want to open, and then click Design on the database window toolbar.
2. In the Database window, click the form or report that contains the SQL statement you want to add parameters to, and open it in Design view.
3. Double-click the form selector or the report selector to open the property sheet.
4. Click the Build button next to the RecordSource property to open the query.
5. Add the columns or expressions that you want to search to the Grid pane.
6. Locate the row containing the data column or expression to search, and then, in the Criteria column, enter one or more unnamed parameters by typing a question mark (?).

When you use an SQL SELECT statement parameter query as the record source of a form or report, you can do one of two things:

• Pass the value of the parameter to a field control on the form or report
• Prompt for the value of the parameter in the Enter Parameter Value dialog box.

To do this, you use the InputParameters property on the form or report, which maps the unnamed parameters in the SQL SELECT statement to expressions that reference the field control in the form or report.

For example, if you have an SQL SELECT statement parameter query that uses the following unnamed parameters:

SELECT State, SalesYear from Orders WHERE Orders.State = ? AND Orders.SalesYear = ?

You can set the InputParameters property as follows:

? char=[Form]![StateList], @SalesYear smallint=[Enter year of interest]

For the first parameter, Access sets the @State parameter to be the current value of the StateList control. For the second parameter, Access prompts the user for the @SalesYear parameter. If there were any other parameters in the SQL SELECT statement, they would have default values assigned.