Sorting Formatted Months?

[jbelf]

Hi,

I have a query and some reports that group by month, which is a date field formated as follows:
Format$([xxxx],"mmmm yyyy&quot

Only problem is Access is sorting this group alphabetically, not by month. So April is first, then August, December, February, etc. Not Jan, Feb, March, etc

Any suggestions?

Many thanks!

 

[PaulBricker]

If I was doing this I would create a new field in the query that just had something like this
MySortField:Month(DateField)

Then do my sort on this field. You might find that you have to use this field in the report also because the Report is going to try and sort your formatted date field alphbetically also. But if you have to add it, you can make it invisible so it works in the background.

Paul

 

[scriverb]

Create another column in your query that contains just the month.

Month([Datefield]) as Month

Now sort your report on this rather than the formated text field. You will now have it sorted numerically by the month.

Post back with any questions.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[markgrizzle]

If your data spans years, then you'll also need to include the year in the sorting column like

SortDate: Year([DateField]) & Month([DateField]).

This way you won't sort

1/98
1/99
2/98