Sorting Dates in crosstab query (1 Viewer)

[PaulJR]

I have a crosstab query that has been working fine from 02-08 to 12-08 (mm-yy) where the dates have been in ascending numerical order. Now we are in 01-09, the query looks like this:

01-09
02-08
03-08...to...
12-08

What I need is this (in date order with 01-09 appearing at the end):

02-08...to...
12-08
01-09

My SQL is:

PARAMETERS [Forms]![Yields]![MfgCell] Text ( 255 ), [Forms]![EFA T2 TOOLS]![ToDate] DateTime, [Forms]![EFA T2 TOOLS]![FromDate] DateTime;
TRANSFORM Count(TestData.FaultyPartType) AS CountOfFaultyPartType
SELECT Format([TestDate],"mm-yy") AS Expr1, Count(TestData.FaultyPartType) AS [Total Of FaultyPartType]
FROM HardwareType INNER JOIN TestData ON HardwareType.HardwareType = TestData.FaultyPartType
WHERE (((TestData.TestDate)>[Forms]![EFA T2 TOOLS]![FromDate] And (TestData.TestDate)<=([Forms]![EFA T2 TOOLS]![ToDate]+1)) AND ((TestData.MfgCell)=[Forms]![Yields]![MfgCell]) AND ((TestData.TestLocation)="Rework") AND ((HardwareType.Level)>2))
GROUP BY Format([TestDate],"mm-yy")
PIVOT TestData.FaultyPartType;

I have tried putting in a new column in the design view of the query to sort on date order, whilst this does effectively sort the dates in the correct order, I lose the grouping of the data, ie I get this:

02-08
02-08
02-08 etc....
03-08 etc etc, until I see a repeat of 01-09 at the end . None of the data is grouped.

Would anybody kindly be able to help me get the TestDate in the correct order whilst maintaining the grouping in this crosstab query?

TestDate is set to datatype Date/Time.

Any help is much appreciated.
Paul

 

[MStef]

Try next:
In query properties populate COLUMN HEADINGS properties, 02-08;03-08;04-08;........01-09

 

[PaulJR]

I tried this but as my TestDate is a Row Heading it doesn't seem to work, also, I would like to keep the automation of the database, as users run the query monthly.

I can't easily change the query, as it is transfered into Excel where VBA goes through the data.

Where I have my expression "SELECT Format([TestDate],"mm-yy") AS Expr1", I think somehow I need to make it a date type then if I put it in accending order I should see 01-09 at the end.

 

[PaulJR]

Phew..!! I have managed to work it out. In case anyone was wondering, I can get the dates in order by changing my expression from 'mm-yy' to 'yy-mm'