Need to Clean Table Data

[BMcC]

I have a customer table that has phone numbers in it. The phone number field
is defined as a text field.
The problem is that over the years some of the phone number have been
entered as (xxx)yyy-zzzz, and other times they've been entered as
xxx-yyy-zzzz-w etc.

I would like to clean up my phone number columns by updating the phone
numbers to all be in a xxx-yyy-zzzz-work format.

This sounds like a good pattern matching problem, probably using vba, but
I'm open to suggestions.

Thanks,

Bob McC
Enright3

 

[Alex Dybenko]

i think you can make a public function in VBA with checks if format is
(xxx)yyy-zzzz
you can use like "(???)???-????"
then convert this format to new one, and finnal use this function in update
query
HTH

 

[John Nurick]

Hi Bob,

If you enjoy pattern matching, use the VBScript regular expression
object. Here's a little VBA function that can be called from an Access
query :

Public Function RegexReplace(V As Variant) As Variant
Dim oRE As Object, varTemp As Variant

Set oRE = CreateObject("VBScript.RegExp")
With oRE
.Global = True
.IgnoreCase = True
.Multiline = False
.Pattern = "foo|bar"
varTemp = .Replace(CStr(Nz(V, "")), "<<$1>>")
End With
RegexReplace= varTemp
Set oRE = Nothing
End Function

In this form it puts << >> round each occurrence of "foo" and "bar";
just modify the .Pattern property and the second argument of the
..Replace method. There's some documentation in the VBScript help file,
and more on the Microsoft website.