Date Format errors pasting into MS ACCESS from Excel

[TGries]

We have a database of information maintained in MS ACCESS. Each month, our report is downloaded into Excel first, for data integrity purposes, then Paste Appended into the Access database.

The problem we are encoutering is this:
The date format from MS Excel does not paste correctly into the table in Access. It either returns no date at all, OR, it paste appends with the year 2020. For example, if I have 11/10/2003 in MS Excel, and paste append into my table, it often comes up as 11/10/2020.

We have been using trial and error on the date. The format in the Access Table for the date is DATE/TIME with Format= Short date.

Please advise on this issue... ray:

 

[SydneyGeek]

The problem we are encoutering is this:
The date format from MS Excel does not paste correctly into the table in Access. It either returns no date at all, OR, it paste appends with the year 2020. For example, if I have 11/10/2003 in MS Excel, and paste append into my table, it often comes up as 11/10/2020.

We have been using trial and error on the date. The format in the Access Table for the date is DATE/TIME with Format= Short date.

Try using DATE/TIME, Medium Date, or create a custom format: dd/mm/yyyy. I'm guessing that Access is seeing 11/10/20 because of the restrictive date format, adn then converting it to 11/10/2020.
Another possibility is that Access is not seeing the Excel data as a proper date at all -- not unusual, unfortunately. You could select the Excel columns and give them a date format, then try again.

Denis