Convert numeric dates to text?

[Guest]

I have an unusal task at hand. We received some data from a customer where
the date of birth is stored as a number. For example, if the birthdate is
7/1/1970 it's stored in the table as 19700701. What's the best way to
convert this to a text value that is formatted as 7/1/1970?

 

[Jeff Boyce]

Jim

First, let's assume (and HOPE!) that all text values are 'zero-padded', so
each "date" has 8 characters (digits). If true, then I believe you could
use the CDate() function to convert those into date/time values.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

If like Jeff said they are all 8 characters and zero padded:

TheDate: Mid(CStr(19700701),5,2) &"/"& Right(CStr(19700701),2) &"/"&
Left(CStr(19700701),4)

 

[Jeff Boyce]

Jim

Oops! My bad!

Jerry provided the DateSerial() approach you can use when all you have is
numbers.

If you had a text string ("February 6, 2006"), you can use the CDate()
function.

Regards

Jeff Boyce
Microsoft Office/Access MVP