"order by date" snafu

M

Matt Edwards

I have a query where one field is a date input mask (ie,
01/22/2004). I'm trying to get it to order by date in
descending order. However, it's ordering in descending
order according to only the first digit - so
that "12/01/2003" is ordered above "01/22/2004" - so that
the January 2004 is right in the middle of January 2003
records.

What do I do to fix it?

Thanks,

-ME
 
R

Roger Carlson

It looks like you are storing these "dates" in text fields. This is exactly
how text fields will sort, ie starting from the first position. There are
two solutions: 1) store the dates in Date/Time fields (this is preferred) or
2) if you must store it in a text field, store it as 2004/12/01 (yyy/mm/dd).
That way they will sort correctly.
 
J

Jeff Boyce

Matt

Dates (Access Date/Time field) and text/characters sort differently. You
describe a sort result that makes me think your "dates" are actually text.

If you need to sort by date/time, you need to store date/time.
 
Top