Changing area code in contact data

[Guest]

After downloading contact info from the Internet into Outlook, I then export
the contacts into Access. Sometimes the original data does not have area
codes (10 digit numbers, only 7 digit numbers). Outlook automatically
inserts the home area code that is in the dialing and setup section in
Windows.

I am trying to do an update query to change the area code but nothing I have
tried works. I do not know vb code. Under the phone field, in the criteria
row, I have tried Left([phone],4=*678; update to 864. This does not seem to
work. Are there any knowledge base articles that show how to change area
codes in contact data?

 

[John Spencer]

Perhaps you want

Calculated Field
Field: Left([phone],4)
Criteria: ="*678"

Field: [Phone]
update to: "864" & Mid([Phone],5)

Or alternative
Field: Phone
Criteria: Like "[*]678*"
Update To: "864" & Mid([Phone],5)

I'm not sure if you are trying to identify phone numbers that start with
*678 or that start with just 678. If this criteria doesn't work for you I
suggest you post an example of two or three numbers that you want to change
and what the result is after the change.

IMPORTANT: TEST this on a copy of your data. The changes are not undoable.

 

[Guest]

These downloaded contacts are automatically dumped into Outlook then exported
into Access. The problem is they are downloaded from sites that list 7 digit
phone numbers. Outlook or Windows inserts my home area code into these
contacts. Before they can be used of course, the area code has to be changed
to the correct one.

The reason for the *678 is to include formatting symbols like parentheses.
Some contacts have them, some don't.

Thanks.

Perhaps you want

Calculated Field
Field: Left([phone],4)
Criteria: ="*678"

Field: [Phone]
update to: "864" & Mid([Phone],5)

Or alternative
Field: Phone
Criteria: Like "[*]678*"
Update To: "864" & Mid([Phone],5)

I'm not sure if you are trying to identify phone numbers that start with
*678 or that start with just 678. If this criteria doesn't work for you I
suggest you post an example of two or three numbers that you want to change
and what the result is after the change.

IMPORTANT: TEST this on a copy of your data. The changes are not undoable.

After downloading contact info from the Internet into Outlook, I then
export
the contacts into Access. Sometimes the original data does not have area
codes (10 digit numbers, only 7 digit numbers). Outlook automatically
inserts the home area code that is in the dialing and setup section in
Windows.

I am trying to do an update query to change the area code but nothing I
have
tried works. I do not know vb code. Under the phone field, in the
criteria
row, I have tried Left([phone],4=*678; update to 864. This does not seem
to
work. Are there any knowledge base articles that show how to change area
codes in contact data?

 

[Guest]

Please explain why Criteria: Like "[*]678*" and why Update To: "864" &
Mid([Phone],5). Why [*] and Mid instead of Left?

Perhaps you want

Calculated Field
Field: Left([phone],4)
Criteria: ="*678"

Field: [Phone]
update to: "864" & Mid([Phone],5)

Or alternative
Field: Phone
Criteria: Like "[*]678*"
Update To: "864" & Mid([Phone],5)

I'm not sure if you are trying to identify phone numbers that start with
*678 or that start with just 678. If this criteria doesn't work for you I
suggest you post an example of two or three numbers that you want to change
and what the result is after the change.

IMPORTANT: TEST this on a copy of your data. The changes are not undoable.

After downloading contact info from the Internet into Outlook, I then
export
the contacts into Access. Sometimes the original data does not have area
codes (10 digit numbers, only 7 digit numbers). Outlook automatically
inserts the home area code that is in the dialing and setup section in
Windows.

I am trying to do an update query to change the area code but nothing I
have
tried works. I do not know vb code. Under the phone field, in the
criteria
row, I have tried Left([phone],4=*678; update to 864. This does not seem
to
work. Are there any knowledge base articles that show how to change area
codes in contact data?

 

[John Spencer]

So what you want is to identify records that start with 678 but may have
leading characters and will be followed by 3 other numbers and then followed
by four other numbers.

Criteria: LIKE "*678*###*####*"

That should find records that have 678 followed by 3 number characters and
four number characters (with or without spaces).

IF you are using Access 2000 (with latest service packs) or a later version
you can use the replace function to replace the 678 with 864

Field: [Phone]
Criteria: LIKE "*678*###*####*"
UpDate To: REplace([Phone],"678","864",1,1,1)

SAME ADVICE as before, test this on a copy of your data to see if it does
what you want.

These downloaded contacts are automatically dumped into Outlook then
exported
into Access. The problem is they are downloaded from sites that list 7
digit
phone numbers. Outlook or Windows inserts my home area code into these
contacts. Before they can be used of course, the area code has to be
changed
to the correct one.

The reason for the *678 is to include formatting symbols like parentheses.
Some contacts have them, some don't.

Thanks.

Perhaps you want

Calculated Field
Field: Left([phone],4)
Criteria: ="*678"

Field: [Phone]
update to: "864" & Mid([Phone],5)

Or alternative
Field: Phone
Criteria: Like "[*]678*"
Update To: "864" & Mid([Phone],5)

I'm not sure if you are trying to identify phone numbers that start with
*678 or that start with just 678. If this criteria doesn't work for you
I
suggest you post an example of two or three numbers that you want to
change
and what the result is after the change.

IMPORTANT: TEST this on a copy of your data. The changes are not
undoable.

After downloading contact info from the Internet into Outlook, I then
export
the contacts into Access. Sometimes the original data does not have
area
codes (10 digit numbers, only 7 digit numbers). Outlook automatically
inserts the home area code that is in the dialing and setup section in
Windows.

I am trying to do an update query to change the area code but nothing I
have
tried works. I do not know vb code. Under the phone field, in the
criteria
row, I have tried Left([phone],4=*678; update to 864. This does not
seem
to
work. Are there any knowledge base articles that show how to change
area
codes in contact data?

 

[John Spencer]

That suggestion was incorrect based on a misunderstanding of your data.

* is a wild card character meaning match any number of characters in this
position (from zero on up).
Like is used with wild cards to tell the compiler that it is to do a wild
card search and not an equals search.

Please explain why Criteria: Like "[*]678*" and why Update To: "864" &
Mid([Phone],5). Why [*] and Mid instead of Left?

Perhaps you want

Calculated Field
Field: Left([phone],4)
Criteria: ="*678"

Field: [Phone]
update to: "864" & Mid([Phone],5)

Or alternative
Field: Phone
Criteria: Like "[*]678*"
Update To: "864" & Mid([Phone],5)

I'm not sure if you are trying to identify phone numbers that start with
*678 or that start with just 678. If this criteria doesn't work for you
I
suggest you post an example of two or three numbers that you want to
change
and what the result is after the change.

IMPORTANT: TEST this on a copy of your data. The changes are not
undoable.

After downloading contact info from the Internet into Outlook, I then
export
the contacts into Access. Sometimes the original data does not have
area
codes (10 digit numbers, only 7 digit numbers). Outlook automatically
inserts the home area code that is in the dialing and setup section in
Windows.

I am trying to do an update query to change the area code but nothing I
have
tried works. I do not know vb code. Under the phone field, in the
criteria
row, I have tried Left([phone],4=*678; update to 864. This does not
seem
to
work. Are there any knowledge base articles that show how to change
area
codes in contact data?

 

[Guest]

Thanks for your help, but I have found some things from previous communities
help postings and your suggestions that helped me put something together that
works for my situation.

In the Criteria row put

Left([phone],4) Or Like ("?678*") Or Like ("678*")

In the Update to row put

"864" & Right([phone],8)

The first line includes the incorrect area code plus left parenthesis. The
Or like statement, I retrieved from some previous help.

The Update to row concatenates the correct area code with the 7 digit phone
number that may include a dash.

This worked this time. I'll know if it works universally when this
situation comes up again.

Thanks for your help.

That suggestion was incorrect based on a misunderstanding of your data.

* is a wild card character meaning match any number of characters in this
position (from zero on up).
Like is used with wild cards to tell the compiler that it is to do a wild
card search and not an equals search.

Please explain why Criteria: Like "[*]678*" and why Update To: "864" &
Mid([Phone],5). Why [*] and Mid instead of Left?

Perhaps you want

Calculated Field
Field: Left([phone],4)
Criteria: ="*678"

Field: [Phone]
update to: "864" & Mid([Phone],5)

Or alternative
Field: Phone
Criteria: Like "[*]678*"
Update To: "864" & Mid([Phone],5)

I'm not sure if you are trying to identify phone numbers that start with
*678 or that start with just 678. If this criteria doesn't work for you
I
suggest you post an example of two or three numbers that you want to
change
and what the result is after the change.

IMPORTANT: TEST this on a copy of your data. The changes are not
undoable.


After downloading contact info from the Internet into Outlook, I then
export
the contacts into Access. Sometimes the original data does not have
area
codes (10 digit numbers, only 7 digit numbers). Outlook automatically
inserts the home area code that is in the dialing and setup section in
Windows.

I am trying to do an update query to change the area code but nothing I
have
tried works. I do not know vb code. Under the phone field, in the
criteria
row, I have tried Left([phone],4=*678; update to 864. This does not
seem
to
work. Are there any knowledge base articles that show how to change
area
codes in contact data?