input mask for phone numbers with/without an extension

[jco]

I am trying to format phone numbers in a text box in a form that will
display the following two different scenarios:


(333) 333-3333 ext 33 or (333) 333-3333

Haven't come up with much luck. One time i was able to do it, it
arranged it really strange in the text box rather than everything flush
left like i like it. Any help would be greatly appreciated!

Thanks

 

[missinglinq via AccessMonster.com]

The problem, of course, is that you really want to have two input masks! I've
tried a number of things, including formatting the data in the BeforeUpdate
Event, but your end users CAN and WILL enter:

8889956677
888-995-6677
(888)995-6677

and, of course, any of the three with an extension, which they'll enter as

ext 1122 or x 1122

The best I've come up with is

" ("999") "999\-9999" ext "9999;;_

This allows an extension not to entered, but does leave the ext haning on the
end. MAybe someone else has a more elegant answer.

BTW, anytime you use an Input Mask, it's always a good practise to do this as
well

Private Sub YourPhoneNumber_Click()
YourPhoneNumber.SelStart = 0
End Sub

that way if they click into the field, say after going off and actualy
finding the phone number, it will be at the beginning of the text box and
won't cause trouble with the input of data.

Good Luck

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[John Nurick]

In my experience input masks are usually more trouble than they're
worth.

Here's a rough VBA function that takes a 10-digit phone number entered
any old how, with an optional extension marked by "x" or "X" or "ext" or
"extension", and formats it the way you want:


Function FormatPhone342(V As Variant) As Variant
Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim S As String

If IsNull(V) Then 'handle null input
FormatPhone342 = Null
Exit Function
End If

Set oRE = CreateObject("VBScript.Regexp")

With oRE
.IgnoreCase = True
.Global = True
'Delete everything except digits and X
.Pattern = "[^0-9x]"
S = .Replace(V, "")

If InStr(1, S, "x", vbTextCompare) > 0 Then
'(Maybe check here that Len(S) > 11 to ensure there
'is a full 10-digit phone number)

'Reformat the number
.Pattern = "(\d{3})(\d{3})(\d{4})x(\d+)$"
S = .Replace(S, "($1) $2-$3 ext $4")
Else
'Maybe check here that Len(S) = 10 to ensure that
'enough digits are present

'Reformat
.Pattern = "(\d{3})(\d{3})(\d{4})$"
S = .Replace(S, "($1) $2-$3")
End If
End With

FormatPhone342 = S
End Function