3

I have a MS Access database to which an excel file is connected to view output. MS Query is used to interface and fetch data based on two conditions date & ID. I am unable to specifiy the parameters in connection properties as Parameters option is disabled.

My requirement is to pass values for the parameters Date & ID from excel and view the output of the query on the same sheet. Thank you...

Improve this question

1 Answer 1

Reset to default
0

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...

  1. Open MS Query
  2. Click the Show/Hide Criteria button (looks like a filter with glasses); this will add the `Criteria section to your query workspace.
  3. Choose a criteria field from your data (date & ID from your example).
  4. In the Value line, enter the name for your parameter, enclosed in square brackets. [Date] and [ID] in your example.
  5. 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.
  6. Return to Excel.
  7. 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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .