Replace column contents with brackets around area code in phone number and other data manipulation

  • Thread starter Thread starter d. smith
  • Start date Start date
D

d. smith

I am new to Access 2007.
I have a Foxpro 2.6 background.

I have imported an Excel spreadsheet into an Access table.

I need to manupulate the column data ie. telephone number for 9996667777 to
become (999) 666-7777

There is some fixing up of the data ie some phone numbers have a 1 stuck in
front so I want to manipulate the data with a series of logic processes to
clean it up, take out unnecesary spaces etc.

What's the easiest way to set his up to run this on a monthly table update.

The Access file is kept as an Access 2003 formatted single table to be used
on a webserver.

Do I have to do this in VBA? Is it best to used some type of group macro?

I have a few columns that need data manipulation before sending the .mdb
file to the webserver.

Dave
 
Dave,

If *all* the data in the column is the same structure, i.e. a string of
10 digits, then it is easy to do with an Update Query. You would simply
update the telephone number column to:

"(" & Left([Phone],3) & ") " & Mid([Phone],4,3) & "-" & Right([Phone],4)

To handle the "1 stuck in front" and "unnecessary spaces", I guess you
could run a couple of other Update Queries in advance, so you would have
a series of 3 Update Queries, for which you could use a macro with an
OpenQuery action to automate.

Update telephone number field to:
Replace([Phone]," ","")

And then, update to:
Mid([Phone],2)
Criteria: Like "1*"

.... and then the one I mentioned in the beginning.

That will be fine if this will cater to all the records, including all
the irregular ones.
 
Back
Top