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:
But then I found a better way:
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
If (a_Month = 0) Then
a_Month = 1
If (a_Month > 12) Then
a_Month = 12
DateFromDMY = CDate(CStr(a_Day) + “/” + CStr(a_Month) + “/” + CStr(a_Year))
Now that’s a crude function, but you get the idea, you can fix up data easily.