>
PARAMETERS Declaration
Description
Declares the name and data type of each parameter in a parameter query.
Syntax
PARAMETERS name datatype [, name datatype [, ...]]
The PARAMETERS declaration has these parts.
| Part | Description |
|
| name | The name of the parameter. Assigned to the Name property of the Parameter object and used to identify this parameter in the Parameters collection. You can use name as a string that is displayed in a dialog box while your application runs the query. Use brackets ([ ]) to enclose text that contains spaces or punctuation. For example, [Low price] and [Begin report with which month?] are valid name arguments. |
| datatype | One of the primary Microsoft Jet SQL data types or their synonyms. |
Remarks
For queries that you run regularly, you can use a PARAMETERS declaration to create a parameter query. A parameter query can help automate the process of changing query criteria. With a parameter query, your code will need to provide the parameters each time the query is run.
The PARAMETERS declaration is optional but when included precedes any other statement, including SELECT.
If the declaration includes more than one parameter, separate them with commas. The following example includes two parameters:
PARAMETERS [Low price] Currency, [Beginning date] DateTime;
You can use name but not datatype in a WHERE or HAVING clause. The following example expects two parameters to be provided and then applies the criteria to records in the Orders table:
PARAMETERS [Low price] Currency,[Beginning date] DateTime;
SELECT OrderID, OrderAmount
FROM Orders
WHERE OrderAmount > [Low price]
AND OrderDate >= [Beginning date];
Specifics (Microsoft Access)
With a parameter query, Microsoft Access prompts you for the criteria when the query is run. This eliminates the extra step of opening the query in Design view and changing the criteria.
The name argument that you supply with the PARAMETERS declaration provides the text that is displayed in a dialog box when you run the query. Microsoft Access automatically creates this dialog box for you.
Using the PARAMETERS declaration in SQL view is equivalent to defining parameters in the Criteria cell of the query design grid.
Example
This example requires you to provide an employee's last name and then uses that entry as the criteria for the query.
PARAMETERS [Enter a Last Name:] Text; SELECT * FROM Employees
WHERE LastName = [Enter a Last Name:];
This example requires you to provide a category ID and then uses that entry as the criteria for the query.
PARAMETERS [Enter a Category ID:] Value;
SELECT DISTINCTROW CategoryID, ProductName,
Count([Order Details].OrderID)
AS Tally FROM Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
GROUP BY CategoryID, ProductName
HAVING CategoryID = [Enter a Category ID:];