Date conversion from YYYYMMDD to DD/MM/YYYY

[a.phillips]

I am trying to convert the date in a table (table name - 2009-2011 event records) with the field name 'Date of registration' with the data type numer and layout YYYYMMDD, into DD/MM/YYYY, does anyone know the code to do this please? I have tried other codes off the internet such as:

Format(DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),"dd/mm/yyyy")

But access isn't allowing me to start the query with Format, any ideas?

Thanks

 

[spikepl]

1. add a new field of type datetime
2. make an update query that sets the value of the new datetime field to the dateserial calculated on the current field
3. delete the number field, AFTER running the query and checking the results

 

[a.phillips]

Thank you very much