update qry to change Excel General to Access Short Date

[mlrucci]

Good afternoon, I am struggling with converting a string (formatted as "general") from an excel spreadsheet to short date in access. Let me explain. The data is coming from a backend source. Unable to change the format. It is importing to an excel spreadsheet in which I am then importing to my db. It is being imported as short text (string). Unable to convert on import. I have in the past done an update qry to convert to short test to short date. There are 129 different spreadsheets which result in 500,000 records. I need have the ability to remove dates outside my date range. In order to do that, I need to convert the date in the imported table to a short date. I have attempted to do the DateValue() (works great on the other tables) and even attempted to append to another table with different format. Unsuccessful to say the least. There are two problems I am encountering.


1. I am unable to convert the original format (2022-01-05 03:35:12.3200000000) to short date
2. When attempting to update, there isn't enough memory. In doing research, it appears the system is attempting to copy the table and with that many records, there isn't enough memory.

Suggestions?

[pbaldy]

How about:

?datevalue(left("2022-01-05 03:35:12.3200000000",10))
1/5/2022

[mlrucci]

Perfect. totally forgot about left. Thank you very much!!!

[pbaldy]

Happy to help!