remove dashes

G

GGill

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

KARL DEWEY

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

GGill

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.
KARL DEWEY said:
Try this ---
Phone Number: Replace(Replace(Replace([YourPhoneField],"-",""),"(",""),")","")

GGill said:
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.
 
J

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]
 
J

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.

GGill said:
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.
KARL DEWEY said:
Try this ---
Phone Number:
Replace(Replace(Replace([YourPhoneField],"-",""),"(",""),")","")

GGill said:
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.
 
J

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)

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

John Spencer said:
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.

GGill said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top