Cross tab report with variable column headings - CrossTabReport.mdb (0/1)

B

Brad

I have spent the past week trying to solve this little problem using a
bit of code.

This is a sales report where the user enters the period and sales rep
and the report generates from the period selected plus each period for
the preceeding 11 months. This requires the cross tab query with
varying headings.

Attached is the database. It uses the basGetPart that I found
somewhere plus code I have written myself.

An autoexec macro starts a form. Select the date 01/01/2003 and sales
rep Slick Willy. This will run the report so it can be viewed as it
should work.

This report works fine if every column has a value in the date
columns. If they do not then an error occurs. Try selecting the date
01/12/2002 to see.

Perhaps there is a better way.

Brad
 
B

Brad

I have spent the past week trying to solve this little problem using a
bit of code.

This is a sales report where the user enters the period and sales rep
and the report generates from the period selected plus each period for
the preceeding 11 months. This requires the cross tab query with
varying headings.

Attached is the database. It uses the basGetPart that I found
somewhere plus code I have written myself.

An autoexec macro starts a form. Select the date 01/01/2003 and sales
rep Slick Willy. This will run the report so it can be viewed as it
should work.

This report works fine if every column has a value in the date
columns. If they do not then an error occurs. Try selecting the date
01/12/2002 to see.

Perhaps there is a better way.

Brad
Click to expand...
 
D

Duane Hookom

You should consider creating "relative" date columns. This method requires
no code anywhere to run
Assume:
Text box containing Last Date of your crosstab: Forms!frmA!txtEndDate
Date field in your table: [MyDate]
Need for 5 columns (days) ending on Forms!frmA!txtEndDate

Create your column headings expression:
ColHead: "Day" & DateDiff("d", [MyDate], Forms!frmA!txtEndDate)
Set the Column Headings property to
Column Headings: Day0, Day1, Day2, Day3, Day4
Select Query | Parameters and enter
Forms!frmA!txtEndDate Date/Time
This will create 5 columns with the values of the Forms!frmA!txtEndDate
stored in Day0.

Your column names will never change. In your report, you can create column
heading labels using text boxes with control sources of:
=DateAdd("D", -0, Forms!frmA!txtEndDate)
=DateAdd("D", -1, Forms!frmA!txtEndDate)
=DateAdd("D", -2, Forms!frmA!txtEndDate)
=DateAdd("D", -3, Forms!frmA!txtEndDate)
=DateAdd("D", -4, Forms!frmA!txtEndDate)
 
B

Brad

Duane,
Hi. Thanks for the info. I understand this now but I am struggling to
work out how to apply the same method except grouping into monthly
buckets.
Any suggestions.
Brad
 
D

Duane Hookom

Do you want to group by month as column headings or row headings. If column
headings, just apply my solution to month increments rather than days.

--
Duane Hookom
MS Access MVP


Brad said:
Duane,
Hi. Thanks for the info. I understand this now but I am struggling to
work out how to apply the same method except grouping into monthly
buckets.
Any suggestions.
Brad

You should consider creating "relative" date columns. This method requires
no code anywhere to run
Assume:
Text box containing Last Date of your crosstab: Forms!frmA!txtEndDate
Date field in your table: [MyDate]
Need for 5 columns (days) ending on Forms!frmA!txtEndDate

Create your column headings expression:
ColHead: "Day" & DateDiff("d", [MyDate], Forms!frmA!txtEndDate)
Set the Column Headings property to
Column Headings: Day0, Day1, Day2, Day3, Day4
Select Query | Parameters and enter
Forms!frmA!txtEndDate Date/Time
This will create 5 columns with the values of the Forms!frmA!txtEndDate
stored in Day0.

Your column names will never change. In your report, you can create column
heading labels using text boxes with control sources of:
=DateAdd("D", -0, Forms!frmA!txtEndDate)
=DateAdd("D", -1, Forms!frmA!txtEndDate)
=DateAdd("D", -2, Forms!frmA!txtEndDate)
=DateAdd("D", -3, Forms!frmA!txtEndDate)
=DateAdd("D", -4, Forms!frmA!txtEndDate)
Click to expand...
Click to expand...
 
Top