loosing the hhmmss when exporting datetime to Microsoft Access through macro (vba and SQL)

Hi All,

I'm using excel to export data from a CSV file to Microsoft Access.

One of the fileds is datetime (3/01/2019 12:02:00 AM). I can import the data into Access manually and the hhmmss remains however when I run it through the macro it is dropping off the hhmmss.

I have attached a sample CSV file, the import spreadsheet and a test Access database (sorry can't attach the Access file).

The data for 02/01/2019 and 03/01/2019 have been manually imported into Access and the data for 04/01/2019 has been imported through Excel.

the project called DataLoad does the loading work and ADOFuncs does the db connection work.


Any help is appreciated as it is driving me crazy.

Cheers

Dean


This is what is imported into Access (03/01/2019 manually imported and 04/01/2019 imported from Spreadsheet)
AccessPic.JPG

Have not looked at your file, but often, when importing data, numbers (and dates/time are numbers) come across as text. Try converting them to date/time 1st?
If you are not sure how to do this, let me know.

If you have text date/time, that wont change no matter how you format it. Even if you format to Date, it will still stay Text.

Try a quick test. Pick a date and use =isnumber() on it
eg =isnumber(A1)
if you get FALSE, you have a text date and it will need to be converted.

When updating the date from excel cell value use VBA Format function to include the date & time.

But in your attached screenshot time is showing as 0:00