"order by date" snafu

  • Thread starter Thread starter Matt Edwards
  • Start date Start date
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
 
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.
 
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.
 
Back
Top