Access date sort problem and solution

Have you run into a problem that the dates or time in a table, query, form or report are sorted as plain text instead of in date order as expected?

Notice that the columns Test1 and Test3 below does not sort correctly.

The reason in this case is that Format has been used and one way to solve it is to use the CDate function to make sure the format of the files is as a date instead of text.

For the Test3 column the solution is to also include a column that is in “Date” format as  the Long Date format cannot be converted to Date format using CDate.

Here is the Access query as SQL:
SELECT DateTest.DateField, Format([DateField],"Short Date") AS Test1, CDate(Format([DateField],"Short Date")) AS Test2, Format([DateField],"Long Date") AS Test3
FROM DateTest;

The following images shows the problem to sort Test1 and that it works with Test2.

Here is the Access query as SQL:
SELECT DateTest.DateField, Format([DateField],"Long Time") AS Test1, CDate(Format([DateField],"Long Time")) AS Test2
FROM DateTest;

If you have problem to sort by date order directly on a table make sure the field Data Type is Date/Time or you need to use a query and convert to date format.

Microsoft Access sort dates or times, according to the Regional and Language Settings of the Control Panel in Windows. This is not so much problem for some date and time formats like Swedish used in Sweden but it sure is with American English format. Something to consider when developing for international customers.

This test is done with Microsoft Access 2007 but the solution to the problem applies to older versions as well.

What sorting trick would you use if you for some reason need to use the Format function?