Search code examples
datems-accessformatimporterror

MS Access: importing Date "3 Dec 2020" into 'date/time' column gives error, how do I get it to recognise it's a date?

I'm very, very new to databases, and trying to collect data from online shops we use. Apologies if this is a repeat question, I couldn't find existing questions that matched my problem for the life of me.

One of the sites we use gives dates in the format "3 Dec 2020" when I download a .csv file.

  • If I try to import into a table whose 'Date' column is formatted "Date/Time", it throws an error and won't log the date information at all.
  • If I import the .csv data when the Access 'Date' column is formatted as a "Short Text" column, and then after it's imported, change it back to "Date" format, it's fine and reformats those dates to 3/12/2020 format.
  • Putting in a space (" "), or "-", as the date delimiter when looking at the advanced import specification doesn't seem to work to get it to log the dates in a column formatted "Date/Time"
  • I tried looking into Masks but was quite confused by it

Could someone help me figure out how to get it directly into a 'Date/Time' column instead of me having to set it as 'short text' and back to 'date/time' again?

Thanks ><

Solution

  • Don't import the file but link it.

    Then, use the linked table as source in a simple select query where you modify and filter the data as needed. For example, both CDate and DateValue will convert a text date to a true DateTime value:

        TextDate = "3 Dec 2020"
    TrueDate = DateValue(TextDate)
    ' TrueDate -> 2020-12-03

    Finally, run an append query using the select query as source to append the data to your table.