ACCESS phone number format

[fido]

I received a database from someone who need help. There are about 3000 records and about 1/2 through they discovered formatting for phone numbers so 1/2 are formatted correctly with area code in parenthises and the other have area code with a dash. I need to search by phone number so I need all of them formatted the same (xxx)xxx-xxx. A simple find and replace does not seem to work and I cannot bet the update query to do it either. Can someon help.

Thanks in advance

 

[EagleKeeper]

Strip off the numbers from right to left.

Take the last four and store them in one field or variable.

Ignore the hyphen then grab the 3 digit prefix.

Ignore the next char then grab the 3 digit area code.

Finally, put it back together the way you wish.