MICROSOFT QUERY 2016
It is not possible to make a parameter query in Office 2016
for Mac. Click
here to vote to restore this functionality.
MICROSOFT QUERY 2011
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.
