I am using Access 2007 to import an Excel file with dates into a table that is all text fields. (The columns within the Excel files move around and there's a separate process that maps these using a schema.)
My problem is that a user may enter a date into an Excel date field as 11/01/09, but sometimes the users have date "formatted" to look like Nov-09 (mmm-yy). After the import the Access table has the value of "Nov-09". When converting this back to a date it becomes 11/09/2010.
How do I import the underlying date value from Excel (40118) into the Access table's text field, if it exists, so that the proper date can be extracted from that field when that data is reformatted? Hopefully it is just a transferspreadsheet switch.
I am using the following to import:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportXLS", .Fields("FullFilePath"), False, .Fields("FileTabName")