Parenthesis as a criteria in a query.

[Guest]

This should be pretty simple.

I am inputing some phone numbers from several diffent spreadsheets into one
table. Different people did the original data entry in a variety of diffent
formats

For Example
718) 555-1212
718.555.1212
718 555 1212
718-555-1212

My boss likes the last format. So I want to build a query to check to make
sure period and parenthesis are not included.

I tried a query wherein I used *)* and "*)*" in the criteria. Both of these
didn't work. What should I use?

 

[Guest]

Use an update query with the Replace function

UPDATE Table1 SET Table1.phoneNum = Replace([Table1]![phoneNum],")","-");

 

[Guest]

Given the variety of formats that may be in the phone number. I don't
believe any one formula will work for all of them. Below is a function that
strips out everything but numbers and returns them formatted like your boss
wants them. Here is how you would use it:

UPDATE MyTable SET PhoneNum = SortString(PhoneNum);

Put the function in a standard module.

Public Function SortString(strOriginal As String) As String
Dim strNumbers As String
Dim strTheChar As String
Dim lngCtr As Long

For lngCtr = 1 To Len(strOriginal)
strTheChar = Mid(strOriginal, lngCtr, 1)
If IsNumeric(strTheChar) Then
strNumbers = strNumbers & strTheChar
End If
Next lngCtr

SortString = Format(strNumbers, "000-000-0000")
End Function

Sorry for the seemingly unrelated naming of the function and variables, but
it was a quick mod of another function that does something similar.

 

[John Vinson]

This should be pretty simple.

I am inputing some phone numbers from several diffent spreadsheets into one
table. Different people did the original data entry in a variety of diffent
formats

For Example
718) 555-1212
718.555.1212
718 555 1212
718-555-1212

My boss likes the last format. So I want to build a query to check to make
sure period and parenthesis are not included.

I tried a query wherein I used *)* and "*)*" in the criteria. Both of these
didn't work. What should I use?

I'd suggest getting rid of ALL the punctuation, and just store
7185551212 in the Table. You can set an Input Mask of 000-000-0000 to
*display* the hyphens.

You can clean up your current data. Make a backup of your database
(this update isn't reversible!) and update the Phone field to

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

If you just want to find all phone numbers containing a parenthesis
and edit them manually, use a criterion of

LIKE "*)*"

The LIKE operator honors wildcards; just using the *)* as a criterion
will look for phone numbers which consist exactly of the
three-character string *)* (and there won't be any of course!)

John W. Vinson[MVP]
John W. Vinson[MVP]

 

[Guest]

That would work, John, but it would not satisfy the OP's desire to have the
format as 555-123-4567

To use your method He/She could add:

Format(Replace(Replace(Replace(Replace([Phone], " ", ""), "-", ""), ")",
""),"(", ""), "000-000-0000")

This should be pretty simple.

I am inputing some phone numbers from several diffent spreadsheets into one
table. Different people did the original data entry in a variety of diffent
formats

For Example
718) 555-1212
718.555.1212
718 555 1212
718-555-1212

My boss likes the last format. So I want to build a query to check to make
sure period and parenthesis are not included.

I tried a query wherein I used *)* and "*)*" in the criteria. Both of these
didn't work. What should I use?

I'd suggest getting rid of ALL the punctuation, and just store
7185551212 in the Table. You can set an Input Mask of 000-000-0000 to
*display* the hyphens.

You can clean up your current data. Make a backup of your database
(this update isn't reversible!) and update the Phone field to

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

If you just want to find all phone numbers containing a parenthesis
and edit them manually, use a criterion of

LIKE "*)*"

The LIKE operator honors wildcards; just using the *)* as a criterion
will look for phone numbers which consist exactly of the
three-character string *)* (and there won't be any of course!)

John W. Vinson[MVP]
John W. Vinson[MVP]

 

[John Vinson]

That would work, John, but it would not satisfy the OP's desire to have the
format as 555-123-4567

It will, if they use the Input Mask.

John W. Vinson[MVP]

 

[John Nurick]

I'd use my rgxReplace() function (at
http://www.j.nurick.dial.pipex.com/Code/index.htm) in an update query.

This should do the job. It looks for the 999 999 9999 pattern (\d means
a digit), ignores everything else, and inserts hyphens.

rgxReplace([PhoneNumber], "^.*(\d{3}).+(\d{3}).+(\d{4}).*$", "$1-$2-$3")

If there's a possibility that the field contains international phone
numbers you'll need to set a criterion that excludes them or they'll be
screwed up.

If the field may contain additional stuff such as extension numbers or
suffixes to indicate "work", "home" etc., the pattern will need to be
modified. This version retains anything after the final 4 digits of the
actual number:
"^.*(\d{3}).+(\d{3}).+(\d{4}.*)$"