Date Order in Crosstab Query

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

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.

--
Duane Hookom
MS Access MVP


"Bill" wrote in message
...
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

On Mon, 3 May 2004 17:24:44 -0700, "Bill" wrote:

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?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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.

-----Original Message-----
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.

--
Duane Hookom
MS Access MVP


"Bill" wrote in message
...
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


.

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.

--
Duane Hookom
MS Access MVP


"Bill" wrote in message
...
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.

-----Original Message-----
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.

--
Duane Hookom
MS Access MVP


"Bill" wrote in message
...
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


.

"Bill" wrote in
:

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