Developer Forum

Access 97- I have a field that contains a date as follows- 17/11/2005.When I
sort by that field, it sorts by day then month then year. Can I get it to
sort by year then month then day?

Change the field into a date/time field, and it will then sort correctly.

Here's a safe way to do it, so you can check the results before you commit
to the new field. This example assumes your existing text date is named
Date1.

1. Open your table in design view.

2. Add a new field of type Date/Time.

3. Save the change, and close the table.

4. Create a new query into this table.

5. Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

6. Drag Date1 into the grid.

7. In the Criteria row under this field, enter:
Is Not Null

8. Drag the new date field into the grid.

9. In the Update row under this field enter:
CDate([Date1])

10. Run the query (Exclamation icon on toolbar.)

Check the table, and make sure that the new field contains the correct
dates. You can then delete the old text field, and sort your query by the
new date field.

Note: From your example, it appears that you use a d/m/y date format. If you
still have problems, see:
International Date Formats in Access
at:
http://www.yqcomputer.com/

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.