How to use a Access Query that as a parameter into Excel database query

[Karen Middleton]

I have an Access Query that joins several Access tables and reports on
it I have a parameter value of Year.Month for this Access query.

I want to use Excel database query from Data > Get External Data > New
Database Query and from here I want to use the above Access query for
the Excel database query but at time of definition Excel complains
"Too few parameters. Expected 1" popup appears. Please let me know how
I can resolve this.

I tried another option I put the MonthYear column into the Access
Query and removed the parameter in the Access query now Excel
retrieves all months data into Excel. I want Excel to prompt me when I
refresh to ask for a specific month and depending what I choose to
only report on it.

Please let me know how I can do this.

Thanks
Karen

 

[Nick Hodge]

Karen

Delete the parameter from Access and save the query. Navigate to the Access
database in Excel (Data>Get External Data> New Database Query...) and get to
the last step in the wizard without selecting any filtering. The last step
has three options, return data, edit data in MS Query or create OLAP. Take
the option 'Edit in MS Query'

You will now get a grid similar to Access's query grid. (You may need to
show the criteria grid (View>criteria)).

Enter the field you want for the criteria and your parameter as in Access,
e.g Between [Enter a start date] And [Enter an end date].

You can now select File>return data to Microsoft Excel.

You will be prompted for your parameter entry. Once this is done you will
get the final dialog asking you where to put the data. On this dialog, press
parameters. You can now select from

1) Being prompted each time
2) Using the values you just entered every time
3) Using cell(s) as the input parameters. (You can also select if you want
to refresh the query when these cell values change).

Take your pick and all should be well.


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)