PARAMETERS Declaration

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

 

Syntax

 

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

 

The PARAMETERS declaration has these parts:

 

Part

Description

name

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.

type

The type of the parameter.

size

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

 

Remarks

 

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];

 

Examples

 

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:

 

PARAMETERS [Name Prefix] TEXT;

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