cross tab query

[Bookbinder75]

I have a cross tab query that has date as a row heading. I now need to put a date parameter onto the cross tab. Thank you.

 

[plnich]

I'm not an "expert" - but I found one solution that works:

Set up a table which holds the parameter. You can then query by this value directly in your cross tab query.

For example to select all records of a certain date, set up a table (TableName: "DateForQuery", with One field name: "SelectedDate&quot Enter the one date you want to select.

In your Crosstab query, in the rowheader field for the date enter in the criteria section:

=DLookUp("[SelectedDate]","DateForQuery&quot

This should work without giving you that annoying error message!

If you have to have it more user ffiendly (ie make entering the date into the table transparent to the user, ) you can also setup a query which updates your table with a date which is prompted for. (i.e. use an updatable query and enter the phrase [Enter Selection Date] in the query- have it update the "DateForQuery" field in your table with this value, then set up a macro which runs your report.This Macro would then run two queries, 1) thequery which updates the table with your parameter, and then the second query which runs the crosstab.

Good Luck!

 

[Bookbinder75]

Thank you this did work. But what if you need the information to be up to date each time you run the query? Any suggestions?

 

[cmmrfrds]

You can make a Public variable and put a Function in the Standard Module to return the date. Prompt the user to enter a date and store it in the public variable. On your report where you want the date to appear put in the controlsource =ReturnParmDate() You could also use this date to search with. In the QDE grid under criteria for a date put =ReturnParmDate()



In the Standard Module put the variable under declarations.
Public ParmDate as Date
Function ReturnParmDate() As Date
' Return the Parameter date from Public variable
ReturnParmDate = ParmDate
End Function