remove dashes

[Guest]

In my table i have phone column. The data format in this column is look
different.
For Example: (800)333-4622, 800323-8800 and 8003233800. But in my form
format should be this way: 800-333-4622. To able to format this way first i
will need to remove all dashes and parentheses and then i will able to format
in my form.
Please show me the code to remove dashes and parentheses in my query.

Thank you.

 

[Guest]

Try this ---
Phone Number: Replace(Replace(Replace([YourPhoneField],"-",""),"(",""),")","")

 

[Guest]

It works, if i have anything in that column,
but if it's no data then when i run query it will say 'error'.
How to add to your code , if is nothing then don't do anything?

Thank you so much for help.

Try this ---
Phone Number: Replace(Replace(Replace([YourPhoneField],"-",""),"(",""),")","")

In my table i have phone column. The data format in this column is look
different.
For Example: (800)333-4622, 800323-8800 and 8003233800. But in my form
format should be this way: 800-333-4622. To able to format this way first i
will need to remove all dashes and parentheses and then i will able to format
in my form.
Please show me the code to remove dashes and parentheses in my query.

Thank you.

 

[John Vinson]

It works, if i have anything in that column,
but if it's no data then when i run query it will say 'error'.
How to add to your code , if is nothing then don't do anything?

Just use a criterion on the Update Query:

LIKE "*[()-]*"

to limit the update to those records which contain a phone number with
one of the unwanted characters.

John W. Vinson[MVP]

 

[John Spencer]

If you are just doing this in the calculated column and don't want to
permanently update the value then use an IIF to test the value of phonefield

IIF(PhoneField Is
Null,Null,Replace(Replace(Replace([YourPhoneField],"-",""),"(",""),")",""))

OR just add a zero length string to PhoneField to force a string to exist
for the replace function

Replace(Replace(Replace([YourPhoneField] & "","-",""),"(",""),")","")

More efficient if you are using an UPDATE query to permanently change the
value is to use John Vinson's suggestion of applying criteria to the field.

It works, if i have anything in that column,
but if it's no data then when i run query it will say 'error'.
How to add to your code , if is nothing then don't do anything?

Thank you so much for help.

Try this ---
Phone Number:
Replace(Replace(Replace([YourPhoneField],"-",""),"(",""),")","")

In my table i have phone column. The data format in this column is look
different.
For Example: (800)333-4622, 800323-8800 and 8003233800. But in my form
format should be this way: 800-333-4622. To able to format this way
first i
will need to remove all dashes and parentheses and then i will able to
format
in my form.
Please show me the code to remove dashes and parentheses in my query.

Thank you.

 

[John Spencer]

If you are sure that the phone length is a max of ten characters, you could
add Right (the expression,10) to get the last ten characters in the string.
This will get up to the rightmost 10 characters. If there are only seven,
then it will return the 7 characters.

Right(Replace(Replace(Replace([YourPhoneField] &
"","-",""),"(",""),")",""),10)

Thank you so much,
the code is working.
How to remove '1' in front of phone number?

If you are just doing this in the calculated column and don't want to
permanently update the value then use an IIF to test the value of
phonefield

IIF(PhoneField Is
Null,Null,Replace(Replace(Replace([YourPhoneField],"-",""),"(",""),")",""))

OR just add a zero length string to PhoneField to force a string to exist
for the replace function

Replace(Replace(Replace([YourPhoneField] & "","-",""),"(",""),")","")

More efficient if you are using an UPDATE query to permanently change the
value is to use John Vinson's suggestion of applying criteria to the
field.

It works, if i have anything in that column,
but if it's no data then when i run query it will say 'error'.
How to add to your code , if is nothing then don't do anything?

Thank you so much for help.
:

Try this ---
Phone Number:
Replace(Replace(Replace([YourPhoneField],"-",""),"(",""),")","")

:

In my table i have phone column. The data format in this column is
look
different.
For Example: (800)333-4622, 800323-8800 and 8003233800. But in my
form
format should be this way: 800-333-4622. To able to format this way
first i
will need to remove all dashes and parentheses and then i will able
to
format
in my form.
Please show me the code to remove dashes and parentheses in my
query.

Thank you.