Changing Data format in a field

N

Nigel

In a query I am importing data from another program and appending it into a
table, The data being imported has phone number fields in the format
(123)456-1234

I need to remove the brackets and the dash from the data so the phone number
is 1234567890

Thanks
 
B

Brendan Reynolds

You can use the Replace() function, replacing the unwanted characters with
an empty string. For example, in an update query ...

UPDATE tblTest SET tblTest.TestText =
Replace(Replace(Replace([TestText],"(",""),")",""),"-","");

See 'Replace function' in the VBA help file for details.
 
J

John Vinson

In a query I am importing data from another program and appending it into a
table, The data being imported has phone number fields in the format
(123)456-1234

I need to remove the brackets and the dash from the data so the phone number
is 1234567890

Thanks
Click to expand...

If you're using A2000 or later, run an Update query updating [Phone]
to

Replace(Replace(Replace([Phone], "(", ""),")", ""), "-", "")


John W. Vinson[MVP]
 
Top