Date format

[dbprogman]

I currently import data into my db however the date format of the db is short date 12/06/2004 but the date format that is imported is 12JUN2005. I need to convert the imported date format to that of my db.
I also believe that the date imported is a string which makes it even harder.

 

[Jon K]

After importing, you can add a new date/time field in the table. Then update the new field with the imported text field in an Update Query e.g.

UPDATE [TableName] SET [NewField] = CDate(Left([ImportedField],Len([ImportedField])-7) & " " & Mid([ImportedField],Len([ImportedField])-6,3) & " " & Right([ImportedField],4));
.

 

[dbprogman]

Thanx Jon K

Your solution worked like a charm thanks heaps only now my date format is around the wrong way should be dd/mm/yyyy but is mm/dd/yyyy.

Ever on the Gold Coast of OZ I'll buy ya beer!!!

 

[filo65]

you could try using:

Format(CDate(....), "dd/mm/yyyy")

filo65

 

[maxmangion]

only now my date format is around the wrong way should be dd/mm/yyyy but is mm/dd/yyyy

You can set that from the Regional Settings in your control panel.

 

[modest]

I prefer using Format() as Filo suggested.

 

[dbprogman]

Format

The Regional settings are correct just Access doesn't want to play that way. will try the format() scenerio.
Thanks All

 

[accesscomthomas]

How to update this text field into date field ?

I'm import a CSV file which has the date in this format

Tue Jul 12 12:32:35 2005, ..., ....
Tue Jul 19 09:36:05 2005, ..., ....

as the import wizard cannot recogize the field and fail to convert it into Date/Time, I import it as a Text field

however, what is the code in Update query such that I can convert it into a new Date/Time field ??

Thanks a lot

 

[accesscomthomas]

oh sorry, I know the solution already

sorry, I figure out the solution already: use a update query as posted and update to :

CDate(Mid([TextDate],5,6) & " " & Right([TextDate],4) & " " & Mid([TextDate],12,8))

thanks a lot anyway