Updating area codes and exchanges

[Guest]

Where can I find info on updating area codes and exchanges through the use of
functions such as MID and Right? I never received training in Excel but
heard these functions can be used to update area codes and exchanges. How do
I find a certain area code, then employ a delete query to delete it?

 

[Rick B]

You never received training in Excel? Are you using Excel?

This is an Access newsgroup. We are here to answer questions about Access
databases and how to create queries in them.

Using Access, if you are saying that an area code has changed, you could use
an update query and the Left or Mid functions. Personally, I have never
heard of a case where an area code changed for everyone in it. I have seen
many cases where a new area code was added to an area. I think (but could
be wrong) that when we added a new area code in Houston many years ago that
some people changed to the new one, and some did not. There would be no way
to fix these in bulk because the changes were based loosely on they physical
location of the person/business. If you changed them all, half of them
would still be wrong.

In any case, post back if you are using Access and need more help.

Post to an Excel newsgroup if you are using Excel.

I would be curious to hear the circumstances that would allow you to use a
batch update process to change an area code, though.

 

[Guest]

Rick I do believe that orignally telephone exchanges were set up as 5 and
then 7 digit numbers. When area codes were added they still used the 7 digit
exchanges so you could chage the area code for all phone numbers that were
the Plasa exchange (began with 75) by setting criteria on those digits.

 

[Guest]

You are correct, Rick. There was a new area code added in Houston. It
created havoc with everyone's database.
We were able to obtain a text file that had all the numbers that were
chaning. That made it pretty easy. Without that, it would be impossible to
get it right.
What part of Houston are you in? I was in The Woodlands until 91 when I
moved to Oregon. Now I'm back in Dallas.
Oregon is beautiful, but it still ain't Texas.

 

[Rick B]

I live near Tomball - almost to the Woodlands. I work at 290 and Hollister.

I've been here most of my life, but travel a lot. If it weren't for family
being here, I can think of a hundred other places I'd like to live.

 

[Guest]

Yes, I am quite aware that this is an ACCESS newsgroup. I believe the
question was about functions that are taught in Excel, but maybe not as often
in Access. The question also asks how to find specific area codes so they
can be deleted. I have seen update queries that were used to update area
codes and exchanges and would like to know where to find information on this
subject. Also, some persons might could suggest some code using LEFT and MID
to accomplish this in an update query.

 

[Guest]

Here is a scenario. A group of contacts were downloaded from various
websites. One particular website posted seven digit phone numbers instead of
10 digit phone numbers. Windows automatically inserted an incorrect area
code with this particular group of contacts, that is why the area code needs
to be changed.

 

[Guest]

What you want to do, based on this info should not be that hard. To come up
with the correct recommendation, some info would be helpful.
Did it insert the same area code for all the numbers?
Are there other phone numbers in the field that have that code as the
correct area code?
What version of Access are you using?
How are you phone numbers formatted in the field?

 

[Guest]

Your right, this should not be that hard.
Yes, it did insert the wrong area code for all the numbers in that group.
Ex. this was a group of Arizona contacts, but my home area code from another
state was inserted. There are other contacts from my home area code in that
field. I am using Access 2000. This field is formatted with 2 parentheses
and a dash. All this should not be necessary to find the code to do an
update query to change area codes.

 

[Guest]

Well, the problem is, since there are other records in the table that have
that area code as a valid area code, you need a way to update only those that
are incorrect and not affect those that are.
Since the bad ones are in AZ, if you have a State field in your table, you
could filter on that. I was hoping you did not carry formatting in the data
(which is always a bad idea), because it just makes chaning the values a
little more work.

Here is a formula that will change the bad area code with the good one:
In the Update To for the field in the query builder:
For example, bad code is 404, good code is 297
Replace([PhoneField], "(404)", "(297)")
Be sure to filter on something that will include only the Arizona records.
Note the Replace function is not available pre 2000.

 

[Guest]

Yes. Thank you. I don't have any code training, so I hope this works.

Well, the problem is, since there are other records in the table that have
that area code as a valid area code, you need a way to update only those that
are incorrect and not affect those that are.
Since the bad ones are in AZ, if you have a State field in your table, you
could filter on that. I was hoping you did not carry formatting in the data
(which is always a bad idea), because it just makes chaning the values a
little more work.

Here is a formula that will change the bad area code with the good one:
In the Update To for the field in the query builder:
For example, bad code is 404, good code is 297
Replace([PhoneField], "(404)", "(297)")
Be sure to filter on something that will include only the Arizona records.
Note the Replace function is not available pre 2000.

Your right, this should not be that hard.
Yes, it did insert the wrong area code for all the numbers in that group.
Ex. this was a group of Arizona contacts, but my home area code from another
state was inserted. There are other contacts from my home area code in that
field. I am using Access 2000. This field is formatted with 2 parentheses
and a dash. All this should not be necessary to find the code to do an
update query to change area codes.