update query and phone/fax format

  • Thread starter Thread starter marilyn
  • Start date Start date
M

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 ().
 
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 ().
Click to expand...

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.
 
Back
Top