Excel Pivot Table - using Access 97 query as Data Source

[gwog]

I'm using Excel 2002 and Access 97.

In access I have a query that combines 3 tables. And limits it to 6 weeks worth of data.

In Excel I created a pivot table using the access query as the data source.

The pivot table works fine if I hard code dates in the criteria of the query (ie. between 11/23/03 and 12/28/03).

But what I would like to be able to do is use a form with a combo box that allows user to select a date. Then have the query use the date selected from combo box and 6 weeks prior to that (all dates are 1 week apart).

The query works fine if I have the following in the criteria of my date field:

Between [Forms]![sdate]![comboSdate] And DateAdd("ww",-5,[Forms]![sdate]![comboSdate])

It gives me exactly the data I need - in access.

But when I try to open the pivot table and refresh the data I get the following error:

"[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1." OK

When I hit OK it says "Problems obtaining data" OK

I hit OK and the pivot table is there - but the data does not get refreshed (ie. the 6 weeks of dates are not correct).

Any suggestions?

Thanks

Lisa.


ps. I've also posted this thread under Access 97 other forum.

 

[SkipVought]

Hi,

Try using MS Query instead of a PivotTable query. In this way you can enter a parameter query and Excel will prompt you for the values.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com