Formatting Phone Number

[David Rivera]

Q: I have phone numbers in a text field with dashes (123-456-7890). I want to
remove the dashes so the phone number data looks like this (1234567890). What
update query will do this?

 

[JohnFol]

Does the form have an input mask or format property set?

 

[David Rivera]

No input mask is set. It is just a text field.

Does the form have an input mask or format property set?

 

[JohnFol]

In the absence of any code, the text is stored exactly as it is typed. It
does sound like there is a Format property set on wherever you use to view
the data.

 

[Mo]

You could try using the Left, Mid and Right functions like this:

Expr1: Left([phoneno],3) & Mid([phoneno],5,3) & Mid([phoneno],10,4)

This is only one way to do it and it's not tested but I think it might work.
If the format of your phone number is not always 999-999-9999, then the SQL
won't work.

 

[David Rivera]

The data was imported from seversl text files, the text files included the
dashes and the phone number field was imported as text. I don't think there
is a format property set.

 

[Ofer]

Try to use replace, you can create a query that displaty both fields

Select [Phone field name], Replace([Phone field name],"-","") as NewPhone
From TableName

You can also update that field based on the replace function

 

[David Rivera]

The replace query worked. Thank you,

Dave

Try to use replace, you can create a query that displaty both fields

Select [Phone field name], Replace([Phone field name],"-","") as NewPhone
From TableName

You can also update that field based on the replace function
--
I hope that helped
Good luck

The data was imported from seversl text files, the text files included the
dashes and the phone number field was imported as text. I don't think there
is a format property set.

 

[Dale]

This is funny... I was just about to ask the opposite question..

My phone numbers are 9999999999 I want to ENTER dashes in the correct
spots. 999-999-9999

What query would I use to format my numbers like that?

Dale

 

[SusanV]

Hi Dale,

To add the dashes:
phone = Format(phone, "000-000-0000")

To remove the dashes:
phone = Replace(phone,"-","")

(Note: all quote marks are double quotes)