Solved Format a 10 digit character text box using Access VBA (1 Viewer)

[david.allan.johnson@redsa]

Hello,

I have a module that goes through a series of characters and removes any characters other than 0,1,2,3,4,5,6,7,8, or 9 , and it's attached to the After Update Event. Is there away to format the series of numbers, after they're filtered, in a certain formation. For ex. after the characters are filtered, they will contact a 10 digit number 8889991010. I would like to format them to (888) 999-1010. Does anyone know if there's away of accomplishing this task using VBA?

Thanks,

 

[theDBguy]

Have you tried the Format() function?

 

[CJ_London]

I was about to suggest the same thing

?format("8889991010","(000) 000-0000")
(888) 999-1010

 

[June7]

Why would there be any non-numeric characters if users are supposed to enter a U.S. phone number? Have you tried textbox InputMask property?

 

[GPGeorge]

These two functions parse, validate and format phone numbers for US format.

Option Compare Database
Option Explicit

Public Function ParseUSPhone(ByRef strPotentialPhone As String, ByVal DisplayStore As String) As String
 
    On Error GoTo errHandler
   
Dim intPhoneLength As Integer
Dim strParsedPhone As String

    ' This constant contains punctuation and characters
    ' that should be filtered from the input string, leaving only digits.
Const CHARS = ".!?,;:""'()[]{}-.abcdefghijklmnopqrstuvwxyz"
Dim intIndex As Integer

    strParsedPhone = strPotentialPhone

    strPotentialPhone = Trim(Replace(strPotentialPhone, _
        vbTab, " "))

    For intIndex = 1 To Len(CHARS)
        strPotentialPhone = Trim(Replace(strPotentialPhone, _
            Mid(CHARS, intIndex, 1), " "))
    Next intIndex

    Do While InStr(strPotentialPhone, " ")
        strPotentialPhone = Replace(strPotentialPhone, _
            " ", "")
    Loop
    ParseUSPhone = strPotentialPhone
    If DisplayStore = "Display" Then 'should the phone be saved with the display formatting
        intPhoneLength = Len(strPotentialPhone)
        Select Case intPhoneLength
            Case 0
                ParseUSPhone = ""
            Case 7 ' exchange and number is okay
                ParseUSPhone = Format(strPotentialPhone, "@@@-@@@@")
            Case 10
                ParseUSPhone = Format(strPotentialPhone, "(@@@) @@@-@@@@")
        End Select
    End If

Cleanup:

    Exit Function

errHandler:
'Replace my error handler with your own version
    Call GlblErrMsg( _
        sFrm:=Application.VBE.ActiveCodePane.CodeModule, _
        sCtl:="ParseUSPhone" _
        )
    Resume Cleanup

    Resume
End Function

Public Function ValidateUSPhone(strPotentialPhone) As Boolean
 
    On Error GoTo errHandler

    ' This constant contains punctuation and characters
    ' that should be filtered from the input string, leaving only digits.
   
Const CHARS = ".!?,;:""'()[]{}-.abcdefghijklmnopqrstuvwxyz"
Dim intIndex As Integer
Dim intPhoneLength As Integer
Dim strParsedPhone As String

    strParsedPhone = strPotentialPhone
    ValidateUSPhone = False

    strPotentialPhone = Trim(Replace(strPotentialPhone, _
        vbTab, " "))

    For intIndex = 1 To Len(CHARS)
        strPotentialPhone = Trim(Replace(strPotentialPhone, _
            Mid(CHARS, intIndex, 1), " "))
    Next intIndex

    Do While InStr(strPotentialPhone, " ")
        strPotentialPhone = Replace(strPotentialPhone, _
            " ", "")
    Loop

    intPhoneLength = Len(strPotentialPhone)
    Select Case intPhoneLength
        Case 0
            If strPotentialPhone = strParsedPhone Then
                ValidateUSPhone = True
            Else
                MsgBox Prompt:="Phone numbers must be formatted as either ""(222) 333-444"" or ""555-6666""", buttons:=vbOKOnly, Title:= "Invalid Phone Number"

            End If
        Case 7 ' exchange and number is okay
            ValidateUSPhone = True
        Case 10 ' exchange and number is okay
            ValidateUSPhone = True
        Case Else  'any other length is invalid
            MsgBox Prompt:="Phone numbers must be formatted as either ""(222) 333-444"" or ""555-6666""", buttons:=vbOKOnly, Title:= "Invalid Phone Number"
    End Select

Cleanup:
   
    Exit Function

errHandler:
' replace with your own error handler
    Call GlblErrMsg( _
        sFrm:=Application.VBE.ActiveCodePane.CodeModule, _
        sCtl:="ValidateUSPhone" _
        )
    Resume Cleanup

    Resume
End Function

 

[david.allan.johnson@redsa]

Have you tried the Format() function?

No, I haven't. Not quite sure how I would set that up. I have a field called officeNum that I'm going to past my 10 digits in. Then I would like to format that 10 digits as (123) 456-7890.

 

[theDBguy]

No, I haven't. Not quite sure how I would set that up. I have a field called officeNum that I'm going to past my 10 digits in. Then I would like to format that 10 digits as (123) 456-7890.

Maybe something like what CJ suggested earlier?

Format([FieldName], "(000) 000-0000")

 

[david.allan.johnson@redsa]

Maybe something like what CJ suggested earlier?

Format([FieldName], "(000) 000-0000")

That worked ! Thanks : > )

 

[theDBguy]

That worked ! Thanks : > )

You're welcome. Glad we could all assist. Good luck with your project.

 

[June7]

Have you done any research on Input Mask?

Did you say "paste" 10 digits, not typing?

 

[Mark_]

No, I haven't. Not quite sure how I would set that up. I have a field called officeNum that I'm going to past my 10 digits in. Then I would like to format that 10 digits as (123) 456-7890.

How are you dealing with extension numbers? i.e. (123) 456-7890 ext. 54321?

 

[david.allan.johnson@redsa]

Have you done any research on Input Mask?

Did you say "paste" 10 digits, not typing?

Yes, I copied and pasted from a webpage a phone number. The problem was that the webpage wasn't consistent and there was different characters in the phone number. For ex. sometimes there were () , - and other times there were -. Just wasn't consistent at all. I created a module that extracted the unwanted characters so it's only a 10 digit number. Then formatted that number in the way I wanted in my DB to make it consistent. The format function was the missing link. Works great now.

 

[CJ_London]

I created a module that extracted the unwanted characters so it's only a 10 digit number

rather than extracting unwanted characters (always a chance there will be a new one and the code needs to be updated) I extract the required characters using a function like this

Function getNumsFromText(s As String) As String
Dim i As Integer
Dim b() As Byte
Dim v As String

    b = StrConv(s, vbFromUnicode)
    For i = 0 To UBound(b)
        If b(i) >= 48 And b(i) <= 57 Then v = v & Chr(b(i))
    Next i
    getNumsFromText = v

End Function

so a call might be

format(getNumsFromText(webtext),"(000) 000-000")

 

[June7]

Agree with CJ - keep numbers and discard everything else. More than one way to accomplish but all most likely need a loop structure. Here's another:

Function GetNums(s As String) As String
Dim x As Integer
For x = 1 To Len(s)
    If IsNumeric(Mid(s, x, 1)) Then GetNums = GetNums & Mid(s, x, 1)
Next
End Function

 

[Pat Hartman]

It is cleaner to just keep the numbers. Set the Format property of the control to format as you want the number displayed. Do NOT use the Format() function to actually embed the special characters.

 

[CJ_London]

It is cleaner to just keep the numbers

Not sure I would agree for telephone numbers - it will have to be stored as text, not numeric because telephone numbers often have a preceding zero - and in the case of international numbers, for the UK instead of 044 you use +44, for the US 001 or +1. May be different in the US but in the UK you have different length numbers 00000 000000 or 000 0000 00000 plus a number of other combinations.

 

[Pat Hartman]

I didn't stay to store the code as a number Numbers are things you do arithmetic with. Codes are always stored as text. Zip codes, SSN's, etc are all strings. Anybody who stored SSN's as numbers got a rude awakening a few years ago when the SSA went to issuing SSN's that included letters. That was the SSA's best option. For most computer systems, changing a numeric field to text would be easier than changing its length.

 

[CJ_London]

I didn't stay to store the code as a number

I didn’t say you did- I just said it would have to be stored as text.

 

[GPGeorge]

I didn’t say you did- I just said it would have to be stored as text.

This is the reason I try to refer to digits vs letters in a text field.

 

[Mark_]

Yes, I copied and pasted from a webpage a phone number. The problem was that the webpage wasn't consistent and there was different characters in the phone number. For ex. sometimes there were () , - and other times there were -. Just wasn't consistent at all. I created a module that extracted the unwanted characters so it's only a 10 digit number. Then formatted that number in the way I wanted in my DB to make it consistent. The format function was the missing link. Works great now.

As your source is a webpage, I'd suggest planning how to deal with non-standard phone numbers now, unless this was a "One off" for initial load. Even so, calling an organization with one main "Number" but where you've got to track extensions will cause you headaches if you don't plan for it ahead of time. Would hate for your next post to be about "So the users have been putting extension / international numbers in a notes field. How to I sift through the notes to put it in my phone number child table?"