Macro to format text to date

[richb]

I have a csv file I will be importing each month. One of the fields is a
date, but it will not let me import the field as a date, I think because some
records have a date and time and others just have a date.

It allows me to import as text, then change the data type to date/time in
design view. I was wondering if this can be done through a macro?

 

[benyod79 via AccessMonster.com]

Import the data, then run an update query after the import.


UPDATE tblName SET tblName.Field = Format(tblName.Field, "mm/dd/yyyy");

I'd definitely suggest getting the data in that specific column uniform. Non-
uniformity creates a lot of headaches, which you're obviously well aware of.

Another option might be to spit the date and time into seperate columns prior
to importing. That way, the column data is consistent to that column. If,
after the split the data is not there, it simply leaves the cell empty.

 

[richb]

The update query does change how it looks in the table but doesn't actually
change the data type to date/time. I have got around the problem by appending
the data to an existing table with the correct format.

Thanks for your help anyway