Big Problem with pivot tables. Need help or even an idea!

[ion97]

I have created a few pivot tables in my database which are based on select queries. All of these queries include a date field
What I want to do, is to have the user to set a date which will filter the query (and accordingly the data viewed on the pivot table)
However: when I use a criteria expression in the query such as "=[forms]![MyForm].[DateOnForm]",
the pivot table cannot be refreshed!!!
And it is really important that the data on the pivotTable is limited to the date selected by the user.

Strangely enough, when I go to the design view of the form and set a date to the criteria line, (not "forms![MyForm].[DateOnForm]" but =#23/3/2001#, the Pivot Table is updated and works allright.

Does anyone know anything, or knows a way to get around this problem?
Any help or idea would be kindly appreciated, sinc I'm desperate!!!!

 

[MarionD]

Have you set the Parameters?

In the design view of the pivot table right mouse click on the gray area, choose parameters from the context menu. Now enter
Forms![Formname] in the Parameter and Date/time in the Type column.

Marion

 

[ion97]

I'm afraid there must be something I'm doing wrong or I didn't understand well. I can't find a contxt menu or any other command that enables me to set parameters to the pivotTable. In the meantime I've tried assigning parameters to the source-Query itself, yet the pivotTable still can not be refreshed!

 

[MarionD]

Hi,
Sorry I think I might have the cat by the tail here! I'm working on a German system and translated Pivot Table to "Kreuztabelle" which when translated back (on second thought) is probably cross tab? I'm sorry if I confused the issue - I'm looking for some info on Pivot Tables and will let you know if I find anything.

A little light humour on the subject of translation:
On entering the idiom "Out of sight, out of mind" into an English to Russian back to English Translation programme guess what came back: "Invisible Maniac"

Marion


[This message has been edited by MarionD (edited 03-26-2001).]