Relative months In reports with cross tab query

[Guest]

I went to this site and did what was suggested and it worked great. Did the
following:
Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=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 works fine when you use a beginning date and an end date that cover any
12 month. However if I try to run the report from 09/01/05 to 9/30/05
my headers appear as:
Oct 04, Nov 04, Dec 04, Jan 05, Feb 05...Sep 05
The data appears in the first column

Running it for 03/01/05 to 05/31/05 produces the follwing results:
Jun 04, Jul 04, Aug 04, Sep 04...Dec04, Jan 05...May 05.
The data appears in the first 3 columns.

I have tried everything to work out the logic, but I've had no luck. Any
suggiestions??

Thanks!

Go to http://groups.google.com/ and enter the line of text beginning Mth1...

Duane Hookom
MS Access MVP

I'm sorry, but I don't understand what you are telling me.

Duane Hookom" wrote:

Search Google Groups on
Mth1 hookom group:*access.reports*
This solution uses relative months as column headings. You can easily
display month names in your report.

Duane Hookom
MS Access MVP

TRANSFORM Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours)
AS
SumOfSumOfWhours
SELECT [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client,
Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS [Total
Of
SumOfWhours]
FROM [subqry:BillableNonBillableDetailbyEmployee]
GROUP BY [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client
PIVOT Format([PriorYear],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Above is the SQL view of my crosstab query. I have a form where the
begin and end dates are entered into a subquery. If the berinning date is
entered as 07/01/04 and the end date is enteted as 06/30/05 tge qyeru
shows the monts acress as Jan, Feb, etc.
I would like to have the months appear as Jul 04,
Aug 04, Sep 04...Jan 05...Jun 05.

How do I do this?

!

 

[Duane Hookom]

What do you want it to do if you don't select 12 months? Why would you want
to create a report like this that shows more or less months? Could you get
by with a 12 month report and a 6 month report?

--
Duane Hookom
MS Access MVP

I went to this site and did what was suggested and it worked great. Did
the
following:
Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=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 works fine when you use a beginning date and an end date that cover
any
12 month. However if I try to run the report from 09/01/05 to 9/30/05
my headers appear as:
Oct 04, Nov 04, Dec 04, Jan 05, Feb 05...Sep 05
The data appears in the first column

Running it for 03/01/05 to 05/31/05 produces the follwing results:
Jun 04, Jul 04, Aug 04, Sep 04...Dec04, Jan 05...May 05.
The data appears in the first 3 columns.

I have tried everything to work out the logic, but I've had no luck. Any
suggiestions??

Thanks!

Go to http://groups.google.com/ and enter the line of text beginning
Mth1...

Duane Hookom
MS Access MVP

I'm sorry, but I don't understand what you are telling me.

Duane Hookom" wrote:

Search Google Groups on
Mth1 hookom group:*access.reports*
This solution uses relative months as column headings. You can easily
display month names in your report.

Duane Hookom
MS Access MVP

TRANSFORM Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours)
AS
SumOfSumOfWhours
SELECT [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client,
Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS [Total
Of
SumOfWhours]
FROM [subqry:BillableNonBillableDetailbyEmployee]
GROUP BY [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client
PIVOT Format([PriorYear],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Above is the SQL view of my crosstab query. I have a form where the
begin and end dates are entered into a subquery. If the berinning date is
entered as 07/01/04 and the end date is enteted as 06/30/05 tge qyeru
shows the monts acress as Jan, Feb, etc.
I would like to have the months appear as Jul 04,
Aug 04, Sep 04...Jan 05...Jun 05.

How do I do this?

!