Adding dash character to existing phone numbers

[Angie M.]

Hi, I'm using Access 03. I have a database with 2300 records in the main
table. It contains multiple phone numbers for each individiual - direct,
mobile, work, fax. I converted the data from another program and it came
over without dashes, so the phone numbers look like this: 3039839838. I
need for them to look like this: 303-983-9838. I've tried the Format
@@@-@@@-@@@@ in both the table and form, but this causes lots of problems,
the dashes "move" when a record is duplicated and are generally not
consistent. The input mask won't work because I've got existing numbers I
need to edit. I tried exporting into Excel to use the custom format, but I
want to use that as a last resort because when I import back from Excel I
loose all my other work on the table data types, lookups, etc.

Is there a way to get those darn dashes into the phone numbers? I tried an
update query also, and find and replace but Access doesn't seem to support
wild cards in the find/replace. Any suggestions?? Thank you

 

[Douglas J Steele]

Try something like:

UPDATE MyTable SET PhoneNumber = Format(PhoneNumber, "@@@-@@@-@@@@")
WHERE Len(PhoneNumber) = 10

(Test on a copy of the table first, just in case...!)

 

[Angie M.]

Thanks but where do put this? In an update query??

 

[Douglas J Steele]

Yes. I gave you the SQL for an Update query.

Create a new query, but don't select any tables. Select SQL View from the
View menu. Type what I have there (replacing MyTable with the appropriate
table name, and PhoneNumber with the appropriate field name)