Make Query Ignore Special Characters

[andy49]

And you'll need to turn as long into as string if you wish to use *like which is a text command.




Sent from my iPhone using Tapatalk

[NadiaVKhan]

Sorry. Its supposed to be just "phone"

There is actually note a <dot> there. Thats just poor picture editing

[jwhite]

Eh? So are you good to go now?

[NadiaVKhan]

I changed

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

To

Public Function GetNumbers(strIn As String) As String
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

Though I am still getting the same error

[jwhite]

When you changed the return to be a String, you should have also changed the line just before Exit Function from CLng(strNum) to just strNum. Here is the function revised:

Public Function GetNumbers(strIn As String) As String
    Dim i As Integer
    Dim strNum As String
    
    On Error GoTo GetNumbers_Error
    
    For i = 1 To Len(strIn)
        If IsNumeric(Mid(strIn, i, 1)) Then
            strNum = strNum & Mid(strIn, i, 1)
        End If
    Next
    
    GetNumbers = strNum
    
GetNumbers_Exit:
    Exit Function
    
GetNumbers_Error:
    Call MsgBox("Error parsing value '" & strIn & "'." & vbCrLf & "Error: " & Err & " - " & Err.Description, vbOKOnly, "GetNumbers():")
    GetNumbers = ""
    Resume GetNumbers_Exit
End Function

[NadiaVKhan]

Thanks. But I get the same error when I try searching for a number in my query

[Bulzie]

What error message are you getting?

[NadiaVKhan]

Sorry!! I thought I had entered it.

"Data type mismatch in criteria expression"


I used

Option Compare Database
Option Explicit

Public Function GetNumbers(strIn As String) As String
    Dim i As Integer
    Dim strNum As String
    
    On Error GoTo GetNumbers_Error
    
    For i = 1 To Len(strIn)
        If IsNumeric(Mid(strIn, i, 1)) Then
            strNum = strNum & Mid(strIn, i, 1)
        End If
    Next
    
    GetNumbers = strNum
    
GetNumbers_Exit:
    Exit Function
    
GetNumbers_Error:
    Call MsgBox("Error parsing value '" & strIn & "'." & vbCrLf & "Error: " & Err & " - " & Err.Description, vbOKOnly, "GetNumbers():")
    GetNumbers = ""
    Resume GetNumbers_Exit
End Function

[Bulzie]

Test to see if doing that in the query even works. Look in your table for an exact value and put that in the Criteria box in the query as a text (with quotes around it) or as a number (without quotes) and see what works. Leave off the Like part and just do the specific value.

[NadiaVKhan]

Hmm.. The query does not work when I remove the LIKE, have quotes, or have no quotes. It will work if I remove the Expr1: GetNumbers([Home Phone]). But thats the whole point.

[andy49]

Initially I'd be tempted to add debug.print strnum just before getnumbers =strnum.

You can then at least see what is being produced by the function in the immediate window.

I should add remover the whole condition too for this test

I'd also like to know the field type of [home number]



Sent from my iPhone using Tapatalk

[Bulzie]

Or take out the Criteria for that field altogether, does it return the correct numeric digits in that field?

[davegri]

Expr1:getnumbers(Cstr([Home Phone]))

[NadiaVKhan]

1. When I run the query without any criteria, I am returned "#Error", "1" (a single digit), or a full number with no special characters (ie: 5550403945).

A. The reason I am receiving single digits is in part due to those Cases of mine which include more than one individual, and thus, more than one phonenumber. For example, If I have 5 individuals attached to one case, Expr1 will return "Case# / Name / 1" & "Case# / Name 2" & "Case# / Name 3" & "Case# / Name 4" & "Case# / Name 5".




2. The data type of [Home Number] as listed in the design view of the table is "short text"

3. I also tried "Expr1:getnumbers(Cstr([Home Phone]))". But that did not make a diffeerence

[andy49]

Are you saying home number can have more than one phone number per line in the table.

I thought it only contained 1 phone number and nothing else in the phone number field.


Sent from my iPhone using Tapatalk