Query a table while ignoring special characters.

Question

Hello

I am trying to conduct a query through the use of a search form on a table that includes a list of phone numbers. These numbers were never standardized, so they look like the following:

5558909989

555-873-0909

555 233 5353

+55 555 594 5943

Say for example, I want to search the second number listed above. It will only appear in my results if I type "555-873-0909". How can I make it appear if I only type "5558730909". In other words, I want the query to ignore the "dashes" when searching through the table.

The query string below is what I have so far. But it does not work.

Answer 1

Add the following function into a standard VBA module

Public Function StripAllChars(strString As String) As String

'Return only numeric values from a string

'Source:

Dim lngCtr As Long

Dim intChar As Integer

    For lngCtr = 1 To Len(strString)

        intChar = Asc(Mid(strString, lngCtr, 1))

        If intChar >= 48 And intChar <= 57 Then

            StripAllChars = StripAllChars & Chr(intChar)

        End If

    Next lngCtr

End Function

Then, instead of querying your fields directly, add a new field

HomePhoneNo: StripAllChars([Home Phone])

and Search it.

Do the same for all your other phone number fields.

This is why it can be beneficial to only store the number, and not the formatting for such fields, and then use the Format control property in forms and reports to display them as you please for visualization.

Answer 2

I see. To do this, would I also need to add this field to my table with the phone numbers? I am querying multiple tables that store phone numbers.

Answer 3

Go on a new blank query field and enter

HomePhoneNo: StripAllChars([Home Phone])

as the Field expression.

Answer 4

Mr Pineault

Forgive me, but I am a little confused about where to add

"HomePhoneNo: StripAllChars([Home Phone])"

You say I should not query my fields directly, but rather, "add a new field." What do you mean by this?

Thanks

Answer 5

Mr Pineault

Thank you very much for this. I was not the one to develop this database. There are a lot of things I wouldve done differently ie: storing only numbers, no " " (spaces).

I appreciate your help