Is is possible to prevent incorrect formatted telephone numbers using vba in a table?

[wedd]

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

[edg126]

Hi, you could try regular expressions to remove any non 0-9 character:

Public Function TransformPhone(stringTest As String)

Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
.multiLine = False
.Global = True
.IgnoreCase = True
.pattern = "[^0-9]"
End With


REMatches = RE.Replace(stringTest, "")

MsgBox REMatches
End Function


Then with the returned result check for lengths. You could also create a database table with the mappings /conversions you want instead of all the like statements (sorry that's vague I'm not exactly sure the best way to do it in vba).

Google implemented a java plugin to transform all phone numbers to international phone numbers that might help for inspiration:

(go to code.google.com and search for libphonenumber)



If you download or browse the source code there is an xml file with all the formats for each country.

[OBP]

wedd, how is the telephone number getting in to the table?
Imported?
Append Query?
Form data entry?

[wedd]

OBP, the numbers are imported through an append query

[OBP]

Can you provide some examples of incorrectly formatted numbers that it does not correct?