Changing Data format in a field

[Guest]

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

 

[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.

 

[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

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

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


John W. Vinson[MVP]