Import Process Changes Year in Date Field

[mizenerman]

I'm importing a csv file that contains patient records into Access 2003. When the file is imported into an existing table it will change the year in the DOB - a date field. For example, 12/20/1928 in the csv file will change to 12/20/2028 once imported. This issue is sporadic and it does not happen to every record. In reviewing the text files is seems to happen to DOB on or before 1930??

The import process uses a spec and the Code Page is OEM United States. I have tested this using other codes (US-ASCII and Unicode) but the issue persists. This is problematic because patient age is a key demographic in our reporting. Has anyone experienced a similar problem and if so what can I do to correct it? Thank you.

 

[dhookom]

Does the import file contain four digits for the year? If not, can it?

These settings are stored in the Control Panel on each PC in the Regional and Language Options.

Duane
Hook'D on Access
MS Access MVP

 

[mizenerman]

Thanks for your reply Duane. I failed to mention the csv is an export from our primary hospital application (Cerner) that was set up by Cerner application development team so I'm unsure what the regional settings are. I do know that when a patient is entered into Cerner the DOB is entered as a four digit year but the csv reflects a two digit year. It's stored as a four digit year in the existing table I dump the data in when imported. The puzzling piece is the randomness.

I'm inclined to write a function that will subtract the year of the DOB from the current year and if it generates a negative value then use a replace function to correct the year. For example, 2011-2029 = -18 (obviously a patient cannot be -18 years old). Your thoughts on that logic?

 

[PHV]

but the csv reflects a two digit year
Yhe issue is here ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

So your statement: "12/20/1928 in the csv file" is incorrect because you also said: "the csv reflects a two digit year", so in your csv you would have: 12/20/28 which later translates to 12/20/2028

But what about the date of 1/1/10? Is it 2010 or 1910? For some really old person or just a todler....?

Have fun.

---- Andy

 

[dhookom]

It's not "random". It's by design. The only reliable solution is to fix the export to include the century.

Duane
Hook'D on Access
MS Access MVP

 

[mizenerman]

Duane, I see that now after researching "two digit years" and learning how they are interpreted. Thanks for pointing me in the right direction.

 

[SkipVought]

Y2K strikes again! ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]