CODEWITHSUNDEEP
ms-accessdatetime
xentia
xentia

Reputation: 435

MS Access date sorting with date/time components combined in one text field

I have a table in MSAccess, which is generated from another software. In that table, there a colmn with date/time elements. Here is a short content:

day,month,year,hour,minute 
1,3,2011,17,21
1,2,2011,18,33

The task is to build MSAccess SQL, which order rows by date/time formed with that data. Is there a way to do that without convert all to seconds?

Thank you in advance

Upvotes: 0

Views: 951

Answers (1)

mwolfe02
mwolfe02

Reputation: 24227

There are several ways to do it without converting to a proper date/time format, but they will all be dog slow. If it is at all possible to convert that field to a proper date/time field (or even add an extra field to hold the properly formatted date/time) then you should do that and be sure that the field is indexed.

If you are truly constrained and have to deal with the table as is, the most straightforward way to do it would be to write a function that translates that string to a date/time and sort by that function.

Here's a quick sample function:

Function ConvertDateTime(dt As String) As Date
Dim Flds As Variant
    Flds = Split(dt, ",")
    ConvertDateTime = DateSerial(Flds(2), Flds(1), Flds(0)) + _
                      TimeSerial(Flds(3), Flds(4), 0)
End Function

Then your query would include the following Order By clause:

ORDER BY ConvertDateTime([day,month,year,hour,minute])

EDIT: Your question is a bit ambiguous as currently asked. If you actually have five separate columns, then the sorting is straightforward: ORDER BY [Year], [Month], [Day], [Hour], [Minute]
I assumed that was not the case or you would have easily figured that out on your own.

Upvotes: 2

Related Questions