Converting csv number to date

[dwhite]

I am working on a query which is based on a table which was populated by a comma deliminted file. One of the fields is a 6 digit number(calculated date field) which I need to convert to a six digit date. Is there a function which will convert that number to a date. I have tried various functions with the expression builder, but no success. The number is 104114 and should convert to an April date in 2004. Thanks for any input.

Dick White
Colorado Springs, CO

 

[RichO]

How do you break up 104114 into an April date? Which numbers specify the month, day, year?

 

[dwhite]

Date Format

From what I understand, the first 3 characters are the number of years since 1900(104) and the 114 is the number of days so far in calender year(April 23)


Dick W.

 

[RichO]

I don't know of any Access built in functions to convert your number to a date but it's pretty easy to do. I wrote this as a function so you can use it from a query:

Public Function ConvertDate(CalcDate As Long) As Date
    Dim yr  As Integer
    Dim dy  As Integer
    yr = Val(Left(CalcDate, 3))
    dy = Val(Right(CalcDate, 3))
    ConvertDate = DateAdd("d", dy, DateAdd("yyyy", yr, #12/31/1899#))
End Function

 

[dwhite]

Thanks for the help

I appreciated the code. It works!

Thanks again.

Dick White