Use SQL to modify text from yyyy/mm/dd to dd/mm/yyyy

[Rangie]

I have imported data into Access and wish to change the format of a field containing a date in YYYY/MM/DD format to DD/MM/YYYY format. If I set the field to a date type the import fails. Is there a way of using an SQL query that would fix the problem.

 

[cmmrfrds]

Can you change the imported file date to be in the format of yyyy-mm-dd or mm/dd/yy which are acceptable date formats to Access. If not then import the date as text into one field and define another field as a type date and run a conversion query to get the text date into a date type field.

Something like.
Update yourtable
Set dateTypeField = (mid(textTypeField,9,2) + "/" + mid(textTypeField,6,2) + "/" + right(textTypeField,4)

 

[Rangie]

Thanks, I have had to look at chaning the date format as I have no control over the data export format.
This is what I typed:
UPDATE [Maximizer notes] Set Date field12 = (mid(text field9,9,2) + "/" + mid(text field9,6,2) + "/" + right(text field9,4)
I get a Syntax error in the UPDATE statement that points to Date in "Set Date Field12"
??

 

[cmmrfrds]

If this is a field name then get rid of the space.
Set Date field12 =
change to.
Set Datefield12 =

 

[LeeJames75]

format([date1],"dd/mm/yyyy") as date2

Lee James
Data Analyst