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