Crosstab Query Column Sort (Access97)

[ready4data]

I'm trying to sort by month and year for a 6 month period in a crosstab query.
Expr1: Format([LAST_SRVC_DATE],"mm/yy") is my column heading sorted ascending.
This is producing 01/02, 02/02, 03/02, 10/01, 11/01, 12/01
Anything I try won't sort it correctly
I want it to display as:
10/01 11/01 12/01 01/02 02/02 03/02
Help! Its late and my brain hurts

[tberndt]

Open your query in design view.

Click on the Column Heading (Crosstab Row) and open Properties.

In Column Headings, type in the dates in the order you want, as in <font color=blue>"11/01","12/01","01/02","02/02"</font color=blue>, etc. Include the quotes (maybe single quotes, to keep things consistent in your query).

HTH,

Tom

[RichUK]

Add the following to the query: Year([YourTbl].[DateField])*12+DatePart("m",[YourTbl].[DateField])-1 and order by that

[ready4data]

Tom,
Should have stated that these aren't static values. Every month a new month is added and the earliest month drops off.
Is there a way to automatically fill the column headings?
Scott

[ready4data]

Rich,
That sorted the colums correctly but the column labels are 24021 24022 24023 24024 24025 24026
not the mm/yy
Scott

[tberndt]

Scott,

Attached is an Access 97 db that shows how to use a rolling crosstab report - it seems to do what you want.

This one is designed for twelve months of data, but you can modify it to meet your needs.

HTH,

Tom

[RichUK]

Don't output the field I gave you, just set it to Where on the total field

[ready4data]

Rich,
When I try to sort on that field I get this message

[ready4data]

Thanks Tom,
I will use that if I can't figure this one out. I already have the dynamic report built.
Just need to sort it correctly
Thanks