wedd
03-28-2011, 10:48 AM
Hi experts! I have an access database that stores incorrected formatted telephone numbers for example: [00-000000-0000], I dont wont to have the number format with dashes included, and some of the telephone numbers within the dbase only contain 5 digits or less. I would like to have the numbers whether national or international formatted in this format [0000 0000 000]. I wrote the following code that suppose to filter out the poor data, via a query but it doesn't automatically prevent incorrect formatted telephone numbers. I was wondering if there was any code you know, or examples or websites that prevent incorrectly formatted telephone numbers that auto-populates correctly formatted numbers, instead of manually changing the data which can be time consuming especially as there are a 1000 different records. Can this be done, if so how? Thanks for your contributions and ideas. :friends: I have a sample of my code below
Public Function spaceTeleNum(telenum) As String
If IsNull(telenum) Then Exit Function
replacethese = " -_()."
For i = 1 To Len(replacethese)
telenum = Replace(telenum, Mid(replacethese, i, 1), "")
Next i
If Len(telenum) = 0 Then Exit Function
If telenum Like "020????????" Or telenum Like "029????????" Then
telenum = Left(telenum, 3) & " " & Mid(telenum, 4, 4) & " " & Right(telenum, 4)
ElseIf telenum Like "011[2-9]???????" Or telenum Like "01[2-9]1???????" Then
telenum = Left(telenum, 4) & " " & Mid(telenum, 5, 3) & " " & Right(telenum, 4)
ElseIf telenum Like "013873?????" Or telenum Like "015242?????" Or telenum Like "01539[4-6]?????" Or telenum Like "01697[3-47]?????" Or telenum Like "01768[347]?????" Or telenum Like "019467?????" Then
telenum = Left(telenum, 6) & " " & Right(telenum, 5)
ElseIf telenum Like "???????????" Then
telenum = Left(telenum, 5) & " " & Right(telenum, 6)
End If
spaceTeleNum = telenum
End Function
Public Function spaceTeleNum(telenum) As String
If IsNull(telenum) Then Exit Function
replacethese = " -_()."
For i = 1 To Len(replacethese)
telenum = Replace(telenum, Mid(replacethese, i, 1), "")
Next i
If Len(telenum) = 0 Then Exit Function
If telenum Like "020????????" Or telenum Like "029????????" Then
telenum = Left(telenum, 3) & " " & Mid(telenum, 4, 4) & " " & Right(telenum, 4)
ElseIf telenum Like "011[2-9]???????" Or telenum Like "01[2-9]1???????" Then
telenum = Left(telenum, 4) & " " & Mid(telenum, 5, 3) & " " & Right(telenum, 4)
ElseIf telenum Like "013873?????" Or telenum Like "015242?????" Or telenum Like "01539[4-6]?????" Or telenum Like "01697[3-47]?????" Or telenum Like "01768[347]?????" Or telenum Like "019467?????" Then
telenum = Left(telenum, 6) & " " & Right(telenum, 5)
ElseIf telenum Like "???????????" Then
telenum = Left(telenum, 5) & " " & Right(telenum, 6)
End If
spaceTeleNum = telenum
End Function