PARAMETERS Declaration

Declares the name and data type of each parameter in a parameter query.




PARAMETERS name type [(size)] [, name type [(size)] ...]


The PARAMETERS declaration has these parts:





The name of the parameter. Use brackets ([ ]) to enclose text that contains spaces or punctuation. For example, [Low price] and [Begin report with which month?] are valid name arguments.


The type of the parameter.


The size of the parameter in characters or bytes for text or binary parameters.




For queries that we run regularly, we 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, our 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.


We can use the PARAMETERS declaration with SELECT, SELECT INTO, INSERT INTO, UPDATE, DELETE, and TRANSFORM statements.


We can include more than one parameter, separating them with commas. The following example includes two parameters:


PARAMETERS [Low price] Currency, [Beginning date] DateTime;


We can use the parameters to substitute any value within the query. We cannot, however, use the parameters to substitute names of columns or tables, or to specify functions or operators. The following example expects two parameters to be provided and then applies the criteria to records in the "Orders" table:


PARAMETERS [Low Freight] Double, [Beginning Date] DateTime;

SELECT [Order ID], [Order Date], [Freight] FROM [Orders] WHERE [Freight] > [Low Freight] AND [Order Date] >= [Beginning Date];




This example requires the user to provide a job title and then selects all employees with the entered title:


PARAMETERS [Employee Title] TEXT;

SELECT [First Name], [Last Name], [Title] FROM [Employees] WHERE [Title] = [Employee Title];


This example requires the user to provide the first characters of the employee name and then selects all employees whose first or last name starts with the entered characters:



SELECT [First Name], [Last Name] FROM [Employees] WHERE [First Name] LIKE [Name Prefix] & "%" OR [Last Name] LIKE [Name Prefix] & "%";