update query and phone/fax format

[marilyn]

I imported 57 different spreadsheets with about that many
options on how the phone and fax numbers were formatted.
I didn't set a default mask for format before importing.
Now that they're in access is there a way to format them
the way I want using an update query? I need dashes, no
spaces, no ().

 

[John Vinson]

I imported 57 different spreadsheets with about that many
options on how the phone and fax numbers were formatted.
I didn't set a default mask for format before importing.
Now that they're in access is there a way to format them
the way I want using an update query? I need dashes, no
spaces, no ().

Are they all ten-digit North American format phone numbers? Many
countries have other numbers of digits and other standard formats.

I'd suggest getting rid of ALL the punctuation; run a few update
queries updating Phone to

Replace([Phone], "(", "")

using ")", "-", " " and any other special characters in the second
argument.

Then set the Format property of the field to

000-000-0000

and watching out for any non-compliant numbers such as 18005551212 or
47423333333.