Dates in Microsoft Access

Every now � then I’m forced to deal with databases in Access, the worst case of this is when the database started life in something else, ‘cos that when Dates usually get really screwy.

I recently had one with dates stored as 3 seperate fileds, I know I should have transformed the data, but I didn’t, so I came up witha few queries that tried to munge the data into something usable, my first attempt was this:

PurchaseDate: LTrim(RTrim(day))+’/’+LTrim(RTrim(month))+’/’+LTrim(RTrim(year))

But then I found a better way:

PDate: DateFromDMY([day],[month],[year])

Where DateFromDMY is a VB function, something akin to:

Public Function DateFromDMY(a_Day As Integer, a_Month As Integer, a_Year As Integer) As Date

If (a_Day = 0) Then

a_Day = 1

End If

If (a_Month = 0) Then

a_Month = 1

End If

If (a_Month > 12) Then

a_Month = 12

End If

DateFromDMY = CDate(CStr(a_Day) + “/” + CStr(a_Month) + “/” + CStr(a_Year))

End Function

Now that’s a crude function, but you get the idea, you can fix up data easily.

Thank you Martin Green for his pointers here.