Date of 1 format problem to 12-31-1899 Excel to Access

[joel009]

I have an import procedure that formats a date to yyyy-mm-dd from a spreadsheet. I get dates globally so I don't know if it is a translation issue. The date is imported into a table and then formatted. From the FAQ file:
FAQ68-5827: Why do Dates and Times seem to be so much trouble?
It says the date will be converted in Excel to a number representing the days from 1-1-1900 with 1 being 1-1-1900.
The date comes in as a 1 but is converting to 12-31-1899. I use SQL to convert it:
DoCmd.RunSQL "UPDATE HACM_Export SET HACM_Export.dAccept = Format([HACM_Export].[dAccept],'yyyy-mm-dd');"
and it gives me 1899-12-31.
I also use SQL to update anything less than 1-1-1911:
DoCmd.RunSQL "UPDATE HACM_Export SET HACM_Export.dAccept = #1900-01-01# WHERE (((HACM_Export.dAccept) Is Not Null And ([HACM_Export].[dAccept])<=#1/1/1911#));"
This does not update the 12-31-1899 to 1900-01-01 either.

Anyone have any ideas?

 

[SkipVought]

http://support.microsoft.com/kb/130514/

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Skip, I checked that reference and did you notice that Excel will store a date as a number and Access will do the same thing but Excel interprets 1 as 1900-01-01 and Access interprets 1 as 1899-12-31.
Am I crazy? That is how I see it and by testing it out I find this to be true. I can import from Excel and the same date in Access will be one day earlier. Thank you Bill Gates.
I will try to put an alpha character in front of the Excel entry then remove the alpha character after import to try to maintain the integrity of the imported data.

Joel

 

[joel009]

I discovered the Dates that are in error have a format of Afrikaans/Date. We ave used my code for over 2 years without a problem like this so I know there is a good format.
Adding an alpha character did not work. I format all fields to text prior to importing but the formating changes the field value to 1.
My problem now is how do I format these fields to English (United Kingdom)/Date format of yyyy-mm-dd prior to importing.

Joel

 

[SkipVought]

I'd convert your Dates to TEXT in Excel. Use an UNAMBIGUOUS format...
[tt]
=TEXT(DateCellRef,"yyyy/mm/dd")
[/tt]

Import into Access. Then convert the TEXT to Date using the DateValue function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}