Invalid phone numbers

[ngarry]

Hi
I am trying to run some queries on a large customer file that has very poor data - particularly in the contact number fields.

If there are more than six "1"s, "9"s, or "0"s in the number, then we are planning to treat it as an invalid number and replace it with a null.

Any ideas about how to do this?
Noel

 

[wazz]

i have a weird idea but need to know how many numbers can there be in each field currently and what is the data type? (check back for other posts.)

 

[ngarry]

Format of field

The field is currently a text field and can have absolutely anything in it!

 

[wazz]

ok. i gave up on the weird idea but maybe this will do it. it will give you the number of 0's 1's or 9's. (there's probably a cleaner way of doing the middle part with all the "elseif's"):

    Dim strPhnNum As String
    Dim i As Integer
    Dim strEntry As String
    Dim intResult As Integer
    
    strPhnNum = "999-444-9999"
    i = 1
    intResult = 0
    
    For i = i To Len(strPhnNum)
        strEntry = Mid(strPhnNum, i, 1)
        If IsNumeric(strEntry) Then
            If strEntry Like "0" Then
            ElseIf strEntry Like "1" Then
            ElseIf strEntry Like "9" Then
                intResult = intResult + 1
            End If
        End If
    Next i
    Debug.Print intResult

 

[Cremate]

The field is currently a text field and can have absolutely anything in it!

Get the string and process it in vba like such:

Set up 3 variables for each number, = 0
For loop through the string character by character
+1 each time you encounter 0, 1, or 9
Reject the string if any of the variables > 6