0

I am importing data from Excel to Access Table using VBA. One of the column has date in the form dd.mm.yyyy but it is stored as short text. How can I change the short text data type to date as dd.mm.yyyy.

Initially I had set the table column as type date but I was not able to import data due to data type mismatch.

Therefore, once the data is in the access table, I would like to change the data type to date with format dd.mm.yyyy.

Asmita
  • 25
  • 1
  • 6
  • Thank you for your help. There was already date in the field as dd.mm.yyyy, When I was trying to convert this column to date using DateValue, it did not work. Now I am replacing dd.mm.yyyy to dd/mm/yyyy and then I am able to convert it to Date using the DateValue() – Asmita Oct 12 '20 at 17:26

1 Answers1

0

Add a new field to the table, TrueDate, and update it using this expression:

DateSerial(Mid([TextDate], 7), Mid([TextDate], 4, 2), Mid([TextDate], 1, 2))
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thank you for your help. There was already date in the field as dd.mm.yyyy, When I was trying to convert this column to date using DateValue, it did not work. Now I am replacing dd.mm.yyyy to dd/mm/yyyy and then I am able to convert it to Date using the DateValue() UPDATE MiddleTable SET F7 = Replace([F7], ".", "/"); UPDATE MiddleTable SET F7 = DateValue([F7]); – Asmita Oct 12 '20 at 17:29
  • 1
    OK. Double-check that _mm_ and _dd_ for `dd <= 12` are not reversed. – Gustav Oct 12 '20 at 17:56