MS Access How do I get months to display in chronological order?

Question

Hi

I have a report that displays information by Months.

The months field is set to Ascending - but this obviously means the months are in alphabetical order, rather than chronological.

How can i fix this, so the report shows it in a chronological order?

Thanks

Adam

Answer 1

Create a new field in the QUERY that drives the report named "SMonth".

SMonth: Format (<<Your date field>>,"yyyymm")

SMonth for Jan 2008 becomes 200801

SMonth for Feb 2008 becomes 200802

Then in the "SORTING AND GROUPING" on your REPORT Add SMonth and set it to Ascending (The field doesn't need to SHOW on the report, just exist in the underlying Data/Query).

The YYYYMM format will always sort properly in either Numeric or Text.

If You use the "M" format it will give You a number, but then sort it as TEXT (Jan = 1, Feb =2, Mar=3, Nov = 11, Dec = 12).  In a TEXT sort it will fall out of order... 1, 11, 12, 2, 3

Hope it helps!

Answer 2

What you will want to end up with is a query that orders the data not by month name but by month number.

It sounds like you store the name of the month rather than the month number (e.g. Jan = 1, Feb = 2 etc) so what you will need to is:

create a column and use a case when to get the month number for a name: case when month = 'January' then 1 when 'February then 2 etc...

The 'case when' is a sql statement and I dont know if it exists in Access