Enabling the Parameters...
option in the Connection Properties
dialog box is very easy-once you figure out, unfortunately, Microsoft documentation is lacking in this area. You can either enable the parameters when you develop your query, or you can add the option after your basic query is established-but either way, access is controlled through MS Query. So...
- Open MS Query
- Click the Show/Hide Criteria button (looks like a filter with glasses); this will add the `Criteria section to your query workspace.
- Choose a criteria field from your data (date & ID from your example).
- In the Value line, enter the name for your parameter, enclosed in square brackets.
[Date]
and [ID]
in your example.
- Rerun your query (using the
!
button), and you'll be prompted for your parameters-enter any value (preferably a valid one) and your returned data should be appropriately filtered.
- Return to Excel.
- You should have access to the Parameters options now. Easiest access is to right click your query's data table and choose
Table > Parameters...
which will enable the Parameters dialog box.
FWIW, I used to use this option alot, but the extra steps in creating an maintaining it became a pain, so now I'll typically write a SQL query to get the info I generally want, and use Excel's Tables to filter, sort and generally manipulate the data I need.