Add / to data

P

pokdbz

I have data like 02042007
I would like to update it so it looks like 02/04/2007 how can I update this
with a query?
 
O

Ofer Cohen

If the date format you are using dd/mm/yyyy then try

DateSerial(right("02042007",4),Mid("02042007",3,2),Left("02042007",2))

If the date format is mm/dd/yyyy then try
DateSerial(right("02042007",4),Left("02042007",2),Mid("02042007",3,2))
 
J

John Spencer

Do you want to UPDATE the field - permanently replace the value or do you
just want to change the way the data is displayed?
Assumption: the value is a string.

To change the display without changing the data
FORMAT([DataValue],"@@/@@/@@@@")

Of course if the data is not exactly 8 characters in length you will get
some unexpected results

To permanently change the data use an update query where you set the field
to the expression above.

To change the datatype, post back for more instructions.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
P

pokdbz

I want to change every value in the field. I tried this in an update query:
DateSerial(right([test],4),Left([test],2),Mid([test],3,2))
But it didn't work.

I want to update and replace the values.
 
Top