About Parameter Queries

What is a parameter query?

If you want to repeatedly run the same query, but with different values at different times, you can use a parameter query. A parameter is a placeholder for a value that you type when the query runs. For example, if you want to find all the customers in a Customers table from the same country/region, but prompt for a different country/region each time, you can type @Enter_Country in the Criteria cell of the Design grid in Query Designer and generate the following SQL SELECT statement:

SELECT * FROM Customers 
WHERE Country = @Enter_Country

The at sign (@) character in front of the parameter, Enter_Country, tells Access to show the Enter Parameter dialog box so you can type in a country/region name, such as "Mexico" or "Denmark", and use that as the value for the WHERE clause.

Using parameters

You can use parameters as placeholders for either text or numeric literal values. Usually, parameters are used as placeholders in search conditions for individual rows or for groups in the WHERE or HAVING clauses of an SQL statement.

You can even use a combination of parameters to specify a range of dates. For example, you can create two parameters - @Enter_Beginning_Date and @Enter_Ending_Date - in the WHERE clause of a query, and then specify a range of hire dates when you run the query as the following example shows.

SELECT * FROM ORDERS
WHERE ShippedDate
BETWEEN @Enter_Beginning_Date AND @Enter_Ending_Date

You can also use parameters as placeholders in expressions. For example, you might want to calculate discounted prices by supplying a different discount value each time you run a query. To do so, you could enter the expression UnitPrice * @Enter_Discount in the Column cell of the Design grid and generate the following SQL SELECT statement as the following expression shows:

SELECT ProductName, UnitPrice,
(UnitPrice * @Enter_Discount) AS DiscountPrice
FROM Products

Comparing named and unnamed parameters

You can specify two types of parameters, named and unnamed.

Named parameters

Named parameters are easier to read and use, and particularly useful if you have multiple parameters in a query and want to make clear what to enter in each parameter. For example, to prompt and search for an author’s first and last names in an authors table, create the following SQL SELECT statement:

SELECT au_id
FROM authors
WHERE au_fname = @Enter_First_Name AND au_lname = @Enter_Last_Name

When you run the paramter query, Access prompts once for each parameter using the parameter name in the Enter Parameter dialog box so a user is clear which name to enter.

Unnamed parameters

You can also enter the question mark (?) character as an unnamed parameter.

For example, if you want to retrieve all authors from one state in a stored procedure, inline user-defined function, or SQL statement in the record source of a form or report, you can type the question mark (?) character in the Criteria cell of the Design grid in Query Designer and generate the following SQL SELECT statement:

SELECT au_lname, au_fname
FROM state
WHERE state = @Param1

In this case, Access will automatically give the unnamed parameter the generated name, @Param1.

Note   Access does not provide a generated name in the case of an SQL Statement in a form or report's record source.

When you can and can't use named parameters

You can use named parameters under the following circumstances:

• When your Microsoft Access project is connected to Microsoft SQL Server database version 2000.
• In the Query Designer of a stored procedure or in-line user-defined function.
• In the SQL Text Editor for a stored procedure, scalar user-defined function, or table user-defined function.

You cannot use named parameters in the SQL statement of a record source that you create in the Query Builder.

You cannot use named or unnamed parameters in a view.