Adding area code to old database

[Uschi via AccessMonster.com]

I am working on an old database where the area code was not a part of the
phone number. Of course, now there is a new record that has an entirely
different area code.

Is there a query I can use to add the area code to the 700+ records already
in the database?

Many thanks,

 

[StrayBullet via AccessMonster.com]

UPDATE tblName SET tblName.txtPhone = "areacode" & [txtPhone];

 

[Steve]

You have to know what area code to assign to each record. If they are all
the same area code, then you really don't need to because you can add it at
runtime whereever you use the telephone number. If they are all the same and
you feel stongly that the telephone numbers need to include the area code,
you can use an update query and update the TelephoneNumber field to"
"(645) " & [TelephoneNumber]
Assuming 645 is the area code of all the telephone numbers.

If there are different area codes you either type them inindividually or if
your records have fields that can be combined to associate with a specific
area code, such as city and state, then you first need to build an area code
table that has records showing the area code for combinations of city and
state. Don't build this table with random cities and states, but use a query
and get all the unique combinations of city and state in your 700+ records
then record the area code for each combination. Once you have the area code
table, create a query that includes your table with 700+ records and the
area cpde table. Join city to city and state to state. Now change the query
to an update query and update the TelephoneNumber field to:
"(" & [AreaCode] & ") " & [TelephoneNumber]

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John W. Vinson]

I am working on an old database where the area code was not a part of the
phone number. Of course, now there is a new record that has an entirely
different area code.

Is there a query I can use to add the area code to the 700+ records already
in the database?

Many thanks,

If the Phone field is big enough (it might be 7 bytes long!) run an Update
query; on the UpdateTo row under the field [Phone] put

"800" & [Phone]

using the actual desired area code instead of 800 of course.

You'll need to decide how or whether to store punctuation in the field; what
I'll usually do is just store a ten-digit number, and add (xxx)xxx-xxxx
punctuation using a Format expression.

John W. Vinson [MVP]