Update query to change phone numbers...

[James Newman]

Hey all,


I hope I have what is a very simple question. I have a field holding
phone numbers that thus far has not been keeping the extra formatting
of an input mask. New conditions have changed such that I now need to
keep this field with all input mask formatting included. For all
future records I DO now know how to accomplish this.

What I DONT know how to do is convert the existing records with bare
numbers to the format of (xxx) xxx-xxxx. I assume that an update
query with some sort of string manipulation is required.

Any help as always is greatly appreciated!


Thanks,
James Newman

 

[fredg]

Hey all,

I hope I have what is a very simple question. I have a field holding
phone numbers that thus far has not been keeping the extra formatting
of an input mask. New conditions have changed such that I now need to
keep this field with all input mask formatting included. For all
future records I DO now know how to accomplish this.

What I DONT know how to do is convert the existing records with bare
numbers to the format of (xxx) xxx-xxxx. I assume that an update
query with some sort of string manipulation is required.

Any help as always is greatly appreciated!

Thanks,
James Newman

An update query will take care of this.

Update YourTable Set YourTable.[PhoneField] =
Format([PhoneField],"(@@@) @@@-@@@@")
Where YourTable.[PhoneField] is not null;

 

[John Spencer (MVP)]

One suggestion.

You might want to check that the current length is 10 characters. Otherwise,
you might end up formatting data that is already formatted and getting some ugly results.

WHERE YourTable.[PhoneField] Like "??????????"

or

WHERE LEN(YourTable.[PhoneField]) = 10

Hey all,

I hope I have what is a very simple question. I have a field holding
phone numbers that thus far has not been keeping the extra formatting
of an input mask. New conditions have changed such that I now need to
keep this field with all input mask formatting included. For all
future records I DO now know how to accomplish this.

What I DONT know how to do is convert the existing records with bare
numbers to the format of (xxx) xxx-xxxx. I assume that an update
query with some sort of string manipulation is required.

Any help as always is greatly appreciated!

Thanks,
James Newman

An update query will take care of this.

Update YourTable Set YourTable.[PhoneField] =
Format([PhoneField],"(@@@) @@@-@@@@")
Where YourTable.[PhoneField] is not null;