Crosstab query and formatted date

[Guest]

I know this is an age old question but I haven't seem to find the answer
quite to my question. The following is my SQL statement for my crosstab query:

TRANSFORM Sum(qry_Measurable_Sum_for_RMA_Dollars.[Total RMA Dollars]) AS
[RMA $]
SELECT qry_Measurable_Sum_for_RMA_Dollars.Plant
FROM qry_Measurable_Sum_for_RMA_Dollars
GROUP BY qry_Measurable_Sum_for_RMA_Dollars.Plant
PIVOT qry_Measurable_Sum_for_RMA_Dollars.[Chart Date];

My [Chart Date] is "mm-dd" so of course with the new year it now puts Jan
dates before Oct, Nov and Dec. I have a subquery to this where I put
[Beg_Week_Date] (which [Chart Date] is based off of) in Ascending order but
it doesn't hold over to the end Crosstab query.

In the crosstab I tried to format the [Beg_Week_Date] for the year in
ascending order, for the Total line I put "Expression" and not "Group By"
becuase when it does "Group By" it looks like:

Plant 12/06 12/13 12/20 01/03 01/10 01/17
1 $87 $359
3 $130 $346
5 $500 $1
1 $178 $1,287 $429
3 $1,354 $15,009 $1,454

So then I put "Expression" or "Where" and I get the error:
"You tried to execute a query that does not include the specified expression
'Format([Beg_Week_Date],"yyyy")' as part of an aggregrate function."

Is there another way? I also know that the format creates a string and that
is why it doesn't put it in order, I am looking for a way around that.

THANKS!
S

 

[Duane Hookom]

This is my standard answer which uses relative dates. You will need to
"re-purpose" the month values to weeks:
=====================================
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 12 months of sales in columns of a crosstab report.
Set the with menuing: 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",.., "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 solution requires no code and will run fairly quickly.
=====================================