reporting with crosstabs

[Chip]

I have created a report that is based on a crosstab
query. The problem I am having is that the result set is
not always constant on my column headings or data. I
would like to create a report format that allows me to
just execute the report and generate the most recent
information from my crosstab query. Unfortunately, as I
create and save the report (and rerun it after result sets
have changed), it now generates an error: The Microsoft
Jet Engine Database doesn't recognize '[09/08/2003]' as a
valid field name or expression. It wouldn't, being that
the information is no longer being pulled up in the
crosstab query on which the report is based. Can someone
help here in getting this report to be more dynamic in
it's creation of field definitions?

 

[Duane Hookom]

Consider creating a report that always has a specific number of dates. The
date range can change but create one report for each different number of
date columns that are required ie: a 10 day report and a 15 day report.
Then create a from "frmA" with a text box "txtEndDate" for the user to enter
the ending date of the report. In the design view of your crosstab, set the
column heading expression to: colhead: "D" & DateDiff("d",[YourDateField],
Forms!frmA!txtEndDate). You will need to select Query|Parameters and enter:
Forms!frmA!txtEndDate Date/Time
Then, if this is for a 10 day report, set the Column Headings property to:
"D9","D8","D7",..."D0"
D0 will be the values for the date entered on the form. D9 will be 9 days
prior.

In your report, use text boxes for the column labels. Set their control
sources to:
=DateAdd("d",-9,Forms!frmA!txtEndDate)
=DateAdd("d",-8,Forms!frmA!txtEndDate)
=DateAdd("d",-7,Forms!frmA!txtEndDate)
....
=DateAdd("d",0,Forms!frmA!txtEndDate)