Problem with sorting date columns in Access 2007

Question

I have created a query that imports data from one table, with dates in the dd/mm/yy hh:mm format and sorts them into columns, with one column by day (dd/mm/yy). However, when the datasheet is displayed, it is sorting by day then month, ie:

 1/1/10, 1/2/10, 1/3/10...2/1/10, 2/2/10 etc (dates in dd/mm/yy).

How can I force it to sort by actual date, ie 1/1/10, 2/1/10, etc?

Many thanks for any help you can offer - I can't find anything on the forums!

Answer 1

Are the dates being stored as dates in a Date field, or as strings in a Text field?

It's important to understand that how Access stores dates and how it displays them are two very different things. In Access, a date is stored as a 8 byte floating point number, where the integer portion of the value represents the date as the number of days relative to 30 Dec, 1899 and the decimal portion represents the time as a fraction of a day. There is no concept of dd/mm/yy (nor mm/dd/yyyy nor yyyy-mm-dd...) If your datesare date fields, you need to ensure that they were, in fact, recognized correctly. Odds are that 2/1/10 was recognized as 1 Feb, 2010, not 2 Jan, 2010. If the dates were recognized correctly, all you should need do is apply the appropriate format to get mm/dd/yyyy.

On the other hand, if the dates are stored in Text field, add a Date field to the table and run an Update query to transfer the date to the new field. You're best off using a custom function to ensure that the date conversion is done correctly, something along the lines of:

Function ddmmyy_to_date(InputDate As String) As Variant

' Assumes InputDate is dd/mm/yy

Dim intDay As Integer

Dim intMonth As Integer

Dim intYear As Integer

Dim varDate As Variant

  If Len(InputDate) = 8

    varDate = Split(InputDate, "/")

    If UBound(varDate) = 2 Then

      intDay = CInt(varDate(0))

      intMonth = CInt(varDate(1))

      intYear = CInt(varDate(2))

      If intYear < 100 Then

        intYear = 2000 + intYear

      End If

      ddmmyy_to_date = DateSerial(intYear, intMonth, intDay)

    End If

  End If

End Function

---

Doug Steele, Microsoft Access MVP

http://www.AccessMVP.com/djsteele (no e-mails, please!)

Co-author, "Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs" (published by Wiley, ISBN 978-0-470-59168-0)