asked on

Auto fix phone numbers in field via update query

Access 2010...
I have a phone number field: formatted as (text) ,
that keeps getting jumbled with many variations of entries.
6XX.581.9998
8XX-3XX-XX74
(206)XXX-9698
2XX/ XXX-78XX

One exception: on international phone calls:
(XXX) 6980-XXXX this is correct

final result should be:
(XXX)<space>2XX-XXX1
OR
for International numbers
(XXX)<space>X287-XXXXXX1

VBA code is great if can be supplied.

Thanks
fordraiders

Jeffrey Coachman

Not really a solution, ...But going forward, you might want to consider using a phone Number "input mask' to this field.
This will force users to enter the data in the correct; order/format.

Now you can wait for an Expert o post a solution to "Fix" the existing data.

Eric Sherman

In addition to Jeffery Coachman's comment ... I would store the international phone numbers in a separate field if possible with input mask also.

ET

SOLUTION

Eric Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

fordraiders,
You can disregard my posts for any points consieration.
I am sure etsherman, can help you through to conclusion.
;-)

Jeff

ASKER CERTIFIED SOLUTION