Convert Decimal Number to Date

[B Melvin]

I've tried to read all of the date conversion posts first, but still need help.

I am using linked tables with dates stored in a decimal format, precision of
8. My biggest problem is that dates are not stored with leading zeroes for
the single-digit months (ie. Jan 1, 2008 is 1012008).

Based on a few posts, I may be SOL, but any suggestions?

Thanks!

 

[Douglas J. Steele]

It's not clear to me what specifically is causing the problem.

If your dates are stored consistently in that format, it shouldn't matter
from the point of view of joining the tables.

If you're trying to convert your numeric field into a date for other
purposes, you can wrap the Format function around the date field:
Format([MyDateField], "00/00/0000")

 

[B Melvin]

Doug:

Thank you!

Your suggestion worked fine. Sometimes we overlook the "easy" solution to
our problem.

Bruce Melvin

It's not clear to me what specifically is causing the problem.

If your dates are stored consistently in that format, it shouldn't matter
from the point of view of joining the tables.

If you're trying to convert your numeric field into a date for other
purposes, you can wrap the Format function around the date field:
Format([MyDateField], "00/00/0000")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've tried to read all of the date conversion posts first, but still need
help.

I am using linked tables with dates stored in a decimal format, precision
of
8. My biggest problem is that dates are not stored with leading zeroes
for
the single-digit months (ie. Jan 1, 2008 is 1012008).

Based on a few posts, I may be SOL, but any suggestions?

Thanks!

 

[John W. Vinson]

On Tue, 3 Jun 2008 08:35:41 -0700, B Melvin <B

I've tried to read all of the date conversion posts first, but still need help.

I am using linked tables with dates stored in a decimal format, precision of
8. My biggest problem is that dates are not stored with leading zeroes for
the single-digit months (ie. Jan 1, 2008 is 1012008).

Based on a few posts, I may be SOL, but any suggestions?

Thanks!

Actually this isn't at all difficult. You can use the MOD function and the
integer divide operator \ to extract the year, month, and day, and use the
DateSerial function to construct a date:

DateSerial([numdate] MOD 10000, [numdate] \ 1000000, [numdate] \ 10000 MOD
100)