Access inaccurately converts date information from Excel

[1VBAnewbie]

I have looked at several posts and I have not been able to find the answer to my situation.

Using MS Excel 2003, I have a worksheet that contains a date entry in the format of mm/dd/yy. When I use the TransferSheet command within an MS Access 2003 function, the value populated within my Access table, named TempLoad, looks OK. However, when I take that date and insert it into a new MS Access table (FinalTbl), the entry becomes 30 Dec 1899.

The column for the Excel spreadsheet has been modified to a text field. The TempLoad Access table is also a text field. I attempt to convert the text field to a date field using the DateValue() function before it is inserted into FinalTbl.

I cannot find out why the value gets converted from the entered date (say 03/05/06) to Dec 30 1899. I am seeking in help that you may provide.

 

[ProgramError]

Could the excel field be a date & time field which you are trying to import into an access date field?


Ian Mayor (UK)
Program Error
If people say I have bad breath, then why do they continue to ask me questions and expect me to answer them?

 

[SkipVought]

Your answer: "The column for the Excel spreadsheet has been modified to a text field."

When you try to import a text field into a numeric field (which a date is BTW), you get ZERO.

ZERO, formatted as a date, is Dec 30 1899

Skip,

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

 

[1VBAnewbie]

Thank you ProgramError and SkipVought.

SkipVought, the Excel field originally was a Date & Time field. When I changed the Excel file type to text, I made the corresponding change to the TempLoad table. That's why I attempted to use the DateValue() function so the text field would be converted to a date field prior to inserting it into FinalTbl.

ProgramError, given the Date & Time field definition in Excel, what datatype option do I have in Excel so it will import as a Date field in Access?

 

[SkipVought]

why would you change a date field to text in the first place? That makes absolutely no sense. I destroys the usefulness of date!!!

convert in Excel & verify.

Skip,

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

 

[1VBAnewbie]

SkipVought,

Believe me, using a text field for a date is not an option I want to exercise. However, in reading other posts on this forum, it was mentioned that Excel and Access handle dates diffently.

I am trying options to find anything that works. I changed the Excel field back to a date datatype with the same problem--the value in FinalTbl is still 30 Dec 1899 while the value in TempLoad (a datatype of date/time) is as entered in the spreadsheet.

If you have input as to how I can get Access to process the date as entered into Excel, please share.

 

[SkipVought]

If I were a betting man, I wager that you still have TEXT in that column. The first few rows of data dictate how the data in the entire column will be interpreted.

Do you have a VALID DATE (format the column as GENERAL to be sure) in every data row?

Skip,

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

 

[PHV]

the value in FinalTbl is still 30 Dec 1899 while the value in TempLoad (a datatype of date/time) is as entered in the spreadsheet
How do you transfer the value from TempLoad to finalTbl ?

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

 

[ProgramError]

I used

DoCmd.TransferSpreadsheet acImport, , _
"table3", "book1", , "A1:G1"

and it worked for me. Just make sure that the field which you are importing into is a date/time field in the table.

Also make sure the date cells in the excel sheet are formatted as date not custom or text.


Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.