Update query for area codes

[Uschi via AccessMonster.com]

I am working on a very old database where the area code was not included with
the telephone number as all of the area codes were the same. I recently had
to add the ( ) to accommodate a new area code.

What I would like to do is include the area code 401 to all of the phone
numbers where the area code has not been filled in.

Can someone help me with this?

Thank you,

 

[MAC]

I am working on a very old database where the area code was not included with
the telephone number as all of the area codes were the same. I recently had
to add the ( ) to accommodate a new area code.

What I would like to do is include the area code 401 to all of the phone
numbers where the area code has not been filled in.

Can someone help me with this?

Thank you,

I would recommend an update query using a if statement
in the update field as listed below.

iif(len([PhoneNo])=7, "401" & [PhoneNo], [PhoneNo])

Basically, you check is the length of the phone number is 7 (does not
have an area code).
If it does not have an area code then you concatinate the Area code
else you leave it the same.

The exact way you do this is dependant on how the number is stored.
Is your phone number
stored as a numeric value or a string. Is the formating (ie '(###)
###-####' ) used or is the user
actually entering the formating?

MAC

 

[Uschi via AccessMonster.com]

The exact way you do this is dependant on how the number is stored.
Is your phone number
stored as a numeric value or a string. Is the formating (ie '(###)
###-####' ) used or is the user
actually entering the formating?

MAC

The number is stored as a numeric value. The formating is (###) ###-####.
Let me give this IF Statement a try.

 

[John W. Vinson]

The number is stored as a numeric value. The formating is (###) ###-####.
Let me give this IF Statement a try.

Change it to Text... you'll never be doing arithmatic with phone numbers, and
Long Integers are limited to 2147483647 so any phone numbers in area codes 215
and up will give you an error message.

John W. Vinson [MVP]