Excel Pivot Table (using Access query as Data)

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

 

[hogroup]

Every time when you adjust your tables and query in Access you need to export it again to excel. then in excel add automatoc update th epivot table when you open it. and check the range of the pivot table