How do I sort an Acess 97 date field ascending by year, day,month

[Guest]

I have a date field in my database formatted as eg 18/08/2005. When doing an
ascending sort the program sorts all entrys by day first, then month and
finally year. The result is that the date of 21/07/06 will appear before
18/08/05.Can I get the ascending sort to go by year, day, month? I use Access
97 in windows 2000.

 

[Guest]

The reason that it sort the date field that way is, when you use the format
function it turns the field into a text field, so you can convert it back to
date after format

Order by CvDate(Format(DateField,"dd/mm/yyyy")

To get the field to display as years month and day and then sort by it, you
can use

Order by Format(dateField,"yyyymmdd")

 

[Allen Browne]

If this is a Date/Time field in your table, you should be able to sort it
correctly in a query with no additional effort. Internally, Access stores a
date/time type as a number, where the integer part represents the date, and
the fraction the time (part of a day). Therefore, it will sort correctly,
independently of the display format.

If you are using a Text type field instead of a Date/Time field, you're
stuck. The best solution is to change it to a Date/Time field, and the
problems are solved.

If this is a calculated field in a query, typecast the calculation using
CVDate(). For example:
DueDate: CVDate([InvoiceDate] + 30)
More on calculated query fields:
http://allenbrowne.com/casu-14.html

Your example indicates that you live in a d/m/y country, so this article
will probably help:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
It explains how to avoid the 3 cases where Access is likely to misinterpet
the date.