Change date in access query

[davidcantor]

I need to change a date field from yyyymmdd to mmddyyyy using an access query, can anyone help me please?

[HansV]

If it is a real date/time field, the format is just a matter of display - whether a date is displayed as 20131203 or as 12032013, the underlying value stored in the table is the same.
If it is a number field or a text field, however, it does matter.
So:
1) What is the data type of the field?
2) What type of field do you want to convert to - should it remain the same or not?

[davidcantor]

it is stored as text and would like to convert it to Date/time format using an update query, thanks

[HansV]

Let's say the current field is named TextDate.
If you want to convert it to a date/time field in a select query, you can use

RealDate: DateSerial(Left([TextDate],4),Mid([TextDate],5,2),Right([TextDate],2))

Set the Format property of this field to the format that you want.

If you want to convert it to a date/time field in the table, you must create this field before running the update query, and set its Format property to the format that you want.
In the update query, add the new field to the query grid.
In the Update to: line, enter

DateSerial(Left([TextDate],4),Mid([TextDate],5,2),Right([TextDate],2))