Ordering of Dates when formatted in a query

[Darragh]

Hi All!

In Access 2003, came across a peculiarity when ordering formatted date
fields.

I have a query which I want to aggregate a number of released legal
decision happening in that month. Each decision is entered into the
database with [decision code], [date released], date released being a
short-date field

I set up an aggregate query

SELECT [decision Code], Format([Date Released],"mmm yyyy") AS
TotalMonth, Count([decision code]) AS CountOfDecisions
FROM [Decisions]
GROUP BY [decisionCode] Format([Date Released],"mmm yyyy")
ORDER BY Format([Date Released],"mmm yyyy");


Query seems to work fine, HOWEVER...the ordering of the date field
orders as if the field was a text field! It doesn't preserve the date
order....

I end up getting the ordered rows like (when ascending)

Apr 2005
Apr 2006
Aug 2005
Aug 2006
Jan 2005

When it should go

Jan 2005
Apr 2005
Aug 2005
Apr 2006
Aug 2006

Anyone come across this before?

Regards
Darragh Murray
http://www.darraghmurray.com

 

[storrboy]

The Format() function returns a string so naturally it can only be
sorted as a string.
You may need to construct it so that the month and year are separate
columns.
You may also have more flexibility in sorting and grouping in a report
than the query itself.

 

[fredg]

Hi All!

In Access 2003, came across a peculiarity when ordering formatted date
fields.

I have a query which I want to aggregate a number of released legal
decision happening in that month. Each decision is entered into the
database with [decision code], [date released], date released being a
short-date field

I set up an aggregate query

SELECT [decision Code], Format([Date Released],"mmm yyyy") AS
TotalMonth, Count([decision code]) AS CountOfDecisions
FROM [Decisions]
GROUP BY [decisionCode] Format([Date Released],"mmm yyyy")
ORDER BY Format([Date Released],"mmm yyyy");

Query seems to work fine, HOWEVER...the ordering of the date field
orders as if the field was a text field! It doesn't preserve the date
order....

I end up getting the ordered rows like (when ascending)

Apr 2005
Apr 2006
Aug 2005
Aug 2006
Jan 2005

When it should go

Jan 2005
Apr 2005
Aug 2005
Apr 2006
Aug 2006

Anyone come across this before?

Regards
Darragh Murray
http://www.darraghmurray.com

The query sorted trhe data correctly.
Format(DateField,"mmm yyyy") returns a string, which is how your data
was sorted.

Just sort on the [DateField] itself.

SELECT [decision Code], Format([Date Released],"mmm yyyy") AS
TotalMonth, Count([decision code]) AS CountOfDecisions
FROM [Decisions]
GROUP BY [decisionCode] Format([Date Released],"mmm yyyy")
ORDER BY [Date Released];