Date Order in Crosstab Query

[Bill]

When I do a crosstab query, the dates do not come out in
ascending date order; for example:

1/12/2004
1/19/2004
1/26/2004
1/5/2004
12/29/2003

The above appears to be sorting left to right. it should
be as below:

12/29/2003
1/5/2004
1/12/2004
1/19/2004
1/26/2004

What's the problem?

Bill

 

[Duane Hookom]

If you are use dates as column headings, the values will sort as text
values. This means 12/31/2004 would appear before 2/1/2003.

 

[John Vinson]

When I do a crosstab query, the dates do not come out in
ascending date order; for example:

Sounds like you may be using the Format() function to format the date;
doing so converts it from a Date/Time to a Text value (which will sort
alphanumerically not chronologically). Care to post the SQL if this
doesn't help?

 

[Bill]

Duane.

You are correct, I am using the date as a column heading.
I'll think about a fix; maybe turning the dates into
week1, week2, week3, .... weekN. If you have a slick
solution, I would appreciate your suggestions.

thanks. bill.

 

[Duane Hookom]

Bill,
Search Google Groups on my name and "Relative dates Mth0" for a solution. If
you can't find this or have questions, come on back.

 

[Tim Ferguson]

You are correct, I am using the date as a column heading.

Try formatting them in ISO form yyyy-mm-dd and they will sort correctly.


Tim F