sorting by date on a formatted date field

[Guest]

I formatted a date field in a crosstab query which I created a report from.
I'm trying to sort the report by date (month) and would like the sort to be
in assending order, but instead of sorting by assending order of the calendar
month, it is sorting by alphabetical order of the calendar month. I am
wondering how to hve it sort by the actual date order.

 

[Allen Browne]

When you say you "formatted" the date in the crosstab, do you mean you use
the Format() function?

The output of the Format() function is text, so it's not surprising you are
getting a text sort rather than a date sort.

Remove the Format() function from the crosstab so the field can behave as a
date. Then in your report, set the Format property of the text box so it
displays as you wish.

 

[Guest]

Hi,
I assume you used the format function to format the date the way you wanted
in a query?
The format function will convert dates to string values, so instead of doing
this in the query set your sorting in the query column as you want and then
do the formatting on the report.
HTH
Good luck

 

[Marshall Barton]

I formatted a date field in a crosstab query which I created a report from.
I'm trying to sort the report by date (month) and would like the sort to be
in assending order, but instead of sorting by assending order of the calendar
month, it is sorting by alphabetical order of the calendar month. I am
wondering how to hve it sort by the actual date order.

Depends on the format you used, but try sorting on the
expression:

=CDate(textdatefield)