Extracting Area Code from Phone #

[cobrajetltf]

I need to update my records with people that's area codes are changing..

Example:

My phone # used to be 412 863-XXXX
now it is 724 960-XXXX

I think I need to create an update query that updates the Area Code(412) & the exchange(863) when my customers phone #'s change..

Anyone know how to do this??

Thanks!!
btw.. I need it for tonight if that is possible!!

 

[cobrajetltf]

To clarify,

When I'm talking about their phone #'s changing, I dont mean when they move, but rather when the phone companies change their area code & prefix #'s as in my case above..

Thanks!!!

 

[JoeMiller]

You can run an update query to do this. Since you didn't say I'm going to assume that you DON'T store the -'s in the field and just numbers. I'm also going to assume that you only store the phone numbers with 10 digits, you always fill in all 10 digits and that you DON'T store any extensions in the field. I'm also going to assume that the name of this field is "PHONE". If I'm wrong about any of this you'll have to adjust accordingly.

Make an update query, drag the Phone field to the grid, and set this in the UpdateTo field:

"412863" & Right([Phone],4)

Add a column and set this as the Field:

LeftDigits: Left([Phone],6)

Set the criteria for this column to:

"724960"

Now you'll have a query that only selects numbers in the Phone field beginning with 724960 and updates them to 412863 and then the last four digits of the number.

HTH Joe Miller
joe.miller@flotech.net

 

[cobrajetltf]

Thank you so much..

Here is the field: VisitorPhone

I have an area code of 701 that the exchanges are 555 & 935, and their area code has to change to 812.

Also, I have an area code of 814 with exchanges of 899 & 277 that have to change to area code 412.

Sorry for leaving out the info Trying to get this database done!

Thanks!!!

 

[THoey]

Still going with a lot of the same assumptions that Joe made:

UPDATE MyAddressBook
SET VisitorPhone = "812" & Right(VisitorPhone, 7)
WHERE Left(VisitorPhone, 6) = "701555" OR
Left(VisitorPhone, 6) = "701935";

UPDATE MyAddressBook
SET VisitorPhone = "412" & Right(VisitorPhone, 7)
WHERE Left(VisitorPhone, 6) = "814899" OR
Left(VisitorPhone, 6) = "814277";

Try putting these queries into a SQL view of a query window and run them. You need to change the table name as I didn't see that in your post. Terry M. Hoey

Please read the following FAQ to learn how to help me help you...

faq183-874