Advanced Database – Create, modify, run a one variable parameter query.

A parameter is a value for a certain field, often used as a query selection, that can be changed each time the query is run.

31 One Variable paramenter

Start by creating a new query in design mode and adding the ‘Jobs’ table.

Select to display all fields from Jobs using the * wildcard in the first query column.

In the second, choose Car Reg No field then input :Car into the Criterion section.

32 Wildcard SQL

The semi colon (:) designates a parameter.

If you switch to view it in SQL mode, notice that the :Car is displayed in a light blue car to represent a parameter.

Save the query and give it a name.

Go back to design view and press F5 to run the query.

33 Paramenter Input

As it is a parameter query the system expects a specific value for the Car Reg No field, so a dialogue box for Parameter Input will open up.

Type in one of the car registration numbers that is included in the table and click OK.

34 Query Design

The query displays all of the records that have the Car Reg No you inputted as the parameter.

If you run the query again, it will reset the parameter and you can enter a new value.

This is a more sophisticated way of searching a table that can be combined with other query commands.