crosstab query sort by date

[Guest]

I created a crosstab query that uses the date as a column heading. I want to
sort the dates in ascending value, I used the following expression:
Expr1: Format([Date],"mm/dd/yy")

The dates sort in order except that jan and feb 05 dates come before dec 04.
Is there any way to fix this so that my query goes in correct order from
12/01/04 through 02/01/05?

Thanks,
c

 

[MGFoster]

I created a crosstab query that uses the date as a column heading. I want to
sort the dates in ascending value, I used the following expression:
Expr1: Format([Date],"mm/dd/yy")

The dates sort in order except that jan and feb 05 dates come before dec 04.
Is there any way to fix this so that my query goes in correct order from
12/01/04 through 02/01/05?

Format is a "display" function, which means its output is a string.
String dates do not sort in chronological order. Try allowing the date
to display in the default date format (IOW, don't try to format the
date) - that way they will sort chronologically.

 

[Edward G]

c,

Hmmmm. Well, in case you haven't already been the online help route,
what Microsoft suggests is entering a string of comma separated dates into
the Column
Headings row of the query's property sheet.
This pretty much works. I have been trying to figure a way to
generate a series of dates in Excel (that part is easy) and
saving them as a CSV file (not having any luck). The CSV
could theoretically be pasted into the Property Sheet of the Xtab query.

I created a crosstab query that uses the date as a column heading. I want to
sort the dates in ascending value, I used the following expression:
Expr1: Format([Date],"mm/dd/yy")

The dates sort in order except that jan and feb 05 dates come before dec 04.
Is there any way to fix this so that my query goes in correct order from
12/01/04 through 02/01/05?

Format is a "display" function, which means its output is a string.
String dates do not sort in chronological order. Try allowing the date
to display in the default date format (IOW, don't try to format the
date) - that way they will sort chronologically.

 

[Guest]

As MgFoster said, Access is sorting it as text. You could add the field as
another column to the query, but leave it as a date or even use the DateValue
function on it, and sort on that but not show it. Then you don't have to
jerk around with that comma crap that Ed talks about.