sorting by date on a formatted date field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

buffis - 09 Jan 2006 15:46 GMT

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.

Reply to this Message

Allen Browne - 09 Jan 2006 16:06 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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
[quoted text clipped - 3 lines]
> month, it is sorting by alphabetical order of the calendar month. I am
> wondering how to hve it sort by the actual date order.

Reply to this Message

freakazeud - 09 Jan 2006 16:08 GMT

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

Signature

Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de

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

Reply to this Message

Marshall Barton - 09 Jan 2006 16:22 GMT

>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)

Signature

Marsh
MVP [MS Access]

Reply to this Message