CrossTab Report

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I have been trying to create a crosstab report, I can
create the inital report easily but my crosstab query has
rolling headings and the report does not reflect this (it
will look for a heading eg: aug 2003, which may not exist
in the query as I look for the past 11 months, next month
a problem will occur with sept 2003 and so on. I think I
have to delete and recreate the report each time?

Can any one help me here?

Thanks

Roger
 
Hi Roger

It is possible to create the report with the desired number of columns, but
leave the text boxes unbound. Use names such as txt1, txt2, etc, and nothing
in their ControlSource property. Then in the Open event of the report, you
can programmatically assign the ControlSource from the fields in the query,
e.g.:
Me.txt1.ControlSource = "aug 2003"
Me.txt2.ControlSource = "sep 2003"
...
or whatever the field is named.
 
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 11 months of sales in columns of a crosstab report.
Set the Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.
Set your queries Column Headings property to:
Mth0, Mth1, Mth2, Mth3,.., Mth10
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
....
This solution requires no code and will run fairly quickly.
 
Thanks but being different headings each month putting a
static control source will not work. I have for the data
field sumofsales and each column heading will be Jan
2004, feb 2004, etc until Aug 2004, september will be
sept 2003. Next month sept 2003, turns into sept 2004,
and I want sept 2003 to disappear.

Any Ideas?

Thanks

Roger
 
The idea was to replace the literals assigned to the ControlSource with the
field names generated at runtime.

Duane's idea is probably easier to implement.
 
Back
Top