Using Microsoft Office for Mac as a Relational Database

By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.

Part 19 - Queries - Parameter Query

It is not possible to make a parameter query in Office 2016 for Mac. Click here to vote to restore this functionality.

A parameter query lets you alter filter criteria criteria without having to modify your SQL query.  A parameter query can be set to use the value of a cell formula as criteria in a SQL query.

From a new worksheet use Get External Data > New Database Query and open the example database. You must use SQL View. You can not use Query View or TEST!

The secret to creating a parameter query is to use = ? in the WHERE clause. There is a space between the equals sign and the question mark.

Copy this query and paste it into SQL View.

SELECT Employees.LastName, Employees.Title FROM Employees WHERE (Employees.Title = ?)

Click the Return Data button

Click the OK button when asked where to put the result set, and a dialog box will appear prompting you to type in the desired employee title. Type Sales Representative, then click the OK button and the result set will appear.

If you misspell Sales Representative you will get an empty set of results. The input is case sensitive.

Once you have created the parameter query you can now fine tune the parameters. Click on any cell in the result set (but not the table headers). On the Data tab of the Ribbon, click the small triangle to the right of the Refresh button and choose Parameters from the pop-up menu.  

In the Parameters dialog you are offered 3 options about what text to use for the filter when the query is refreshed when the Refresh button is clicked. Use the Parameters dialog to edit the prompt presented when refreshing data.

Prompt for value using the following string: The text you enter here will be displayed in the prompt dialog when the query is refreshed,

Fixed Value:  You set a permanent value to always use when refreshing the query.

Use text from a cell: Specify which cell's value will be used as the filter criteria. This cell can be the result of a calculation. You can click the small button at the right of the input field to navigate to a cell or type the cell's value. The value can be from a different worksheet.

You can have more than one parameter condition in the WHERE clause using AND and OR logical operators.

Parameter query screen shot

Part 1
Part 18 Part 20 (Next)