Make Query Ignore Special Characters

[NadiaVKhan]

Hello,

I am trying to write a query that will find phone numbers in a database. However, this database was not constructed... the best. As a result, phone numbers can be written in any way such as "1234567890; 123-456-7890; (123) 456 7890 ; 123 456 7890; etc.)

Is there a way I could run a query so it ignores dashes, parenthesis, spaces, etc.?

[andy49]

I'd say create a vba function

Function totrim (trimtxt as string) as string

Trimtxt = Replace (trimtxt, "-", "")
Trimtxt =Replace (trimtxt, " ", "")

And so on

Totrim= trimtxt

End function

You can call the function from a query so long as it's in a module.


Sent from my iPhone using Tapatalk

[andy49]

Alternatively

Function totrim(trimtxt as string) as string

If len(trimtxt)=0 then
Totrim =""
Else

Dim I as integer
For I = 1 to len (trimtxt)

If (isnumeric(mid(trimtxt,I,1)) = false then

Replace(trimtxt, mid(trimtext,I,1), "*")

Endif

Next I

Replace (trimtxt, "*", "")

Totrim = trimtxt
Endif

End function

Will strip out all non numeric values


Sent from my iPhone using Tapatalk

[NadiaVKhan]

Perfect! Thank you!. This is what Ive made:

Public Function PhoneTrim (TrimTxt as string)
Trimtxt = Replace (trimtxt, “-”, “”)
Trimtxt = Replace (trimtxt, “(”, “”)
Trimtxt = Replace (trimtxt, “)”, “”)
Trimtxt = Replace (trimtxt, “_”, “”)
Trimtxt = Replace (trimtxt, “ ”, “”)
Trimtxt = Replace (trimtxt, “+”, “”)
Trimtxt = Replace (trimtxt, “*”, “”)
 
PhoneTrim= TrimTxt
End Function

This is one of the first codes Ive made. You say I can call the function from a query so long as its in a module. But how do I run it from a module? I have never done that before.

[jwhite]

With your Public Function in a standalone module, you can call it anywhere in code with:

strVariableName = PhoneTrim(strText)

or in a query with: YourFieldNameTrimmed: PhoneTrim([YourFieldName])

At that end of your procedure declaration be explicit by adding As String to return a String value:

Public Function PhoneTrim (strText As String) As String

[Bulzie]

Another way might be to use the Val function to return only numbers in a string.
x = Val(trimtxt)

[andy49]

In the query builder, look for functions then the functions from your db

You'll see the function phonetrim.

Click on it and you'll be asked to add phone number field.

Expr: phonetrim(phonenumber) will appear in the query design.


Sent from my iPhone using Tapatalk

[NadiaVKhan]

So, in the design view of my query....

I have



When I try to run the query, I am told "undefined function modPhoneTrim in expression.

[andy49]

Can someone help here as I can't see the image on Tapatalk


Sent from my iPhone using Tapatalk

[Bulzie]

Don't see an image.

Just for alternate coding sake, here is another piece of code I found to just get the numeric digits instead of trying to replace all special characters.

Public Function GetNumbers(strIn As String) As Long
Dim i As Integer
Dim strNum As String

On Error GoTo num_err

For i = 1 To Len(strIn)

If IsNumeric(Mid(strIn, i, 1)) Then

strNum = strNum & Mid(strIn, i, 1)

End If

Next

GetNumbers = CLng(strNum)
Exit Function

num_err:
GetNumbers = -1

End Function

[jwhite]

> When I try to run the query, I am told "undefined function modPhoneTrim in expression

The function name is PhoneTrim, and I presume you saved it in a module named modPhoneTrim. Therefore in the query (or call from code) you would use just PhoneTrim(....

[NadiaVKhan]

Thank you everyone!!!

And sorry about the picture. I forgot that this website doesnt let me do a ctrlV of a picture.

And Mr. Bulzie, I decided to use your code instead as I imagine it would be more "all-encompassing" than mine.

And Mr. JWhite, thank you for clearing that up. I was typing modPhoneTrim(...) originally.

Now, I have run the query... and It... sort of works. But I think I may not have made myself clear.


Lets say I want to search all PhoneNumbers in my Database. The Number I am looking for is (555)-123-4567.

Now, lets say that I try searching only a segment of that number. Namely: "5123" into my query.

So my query looks like:


.

This query will NOT pickup (555)-123-4567. But it will pickup other numbers such as 555-777-5123 or 555-646-5123

[jwhite]

Move the like statement under Expr1 and it should work.

[NadiaVKhan]

I tried that. But I get the error: "Date mismatch in criteria expression"

[andy49]

The field should be [.phone] or [phone]




Sent from my iPhone using Tapatalk