Stripping Input Strings

[grep]

I have a phone number field on a form, and I have the phone numbers
formatted as (@@@) @@@-@@@@. This works great if I'm entering the phone
number manually. But often, I use cut-n-paste from elsewhere to put in
the phone number, and it's usually formatted.

What I'd like to do is code the field to strip out any non-numeric
characters when the number is input. Then the formatting can work
properly, the angels will sing and peace will reign on Earth. Alright,
one of out three anyway...

Any suggestions as to how (and where) to do that?

grep

 

[Albert D.Kallal]

I have a phone number field on a form, and I have the phone numbers
formatted as (@@@) @@@-@@@@. This works great if I'm entering the phone
number manually. But often, I use cut-n-paste from elsewhere to put in the
phone number, and it's usually formatted.

What I'd like to do is code the field to strip out any non-numeric
characters when the number is input. Then the formatting can work
properly, the angels will sing and peace will reign on Earth. Alright, one
of out three anyway...

Any suggestions as to how (and where) to do that?

You got the right approach and idea (in fact, the above is exactly what I
do). Here is a routine that will work for all your phone number fields.

For each fax, phone, pager control on your form, simply put in the after
update event setting the following:


=MyPhoneFormat()

The beauty of the above approach is that you can actually highlight the
phone, fax, pager fields on the form, and type in the above function name to
run (you can thus "SET" the 3, 4 fields all at once, and NOT have to
actually open up each control, and put in the function name you want the
control to run.

Then place in a standard module, the following two routines...notice how he
code picks up the control..so you have a "general" routine that just
works...


Public Function MyPhoneFormat()

Dim ctlAct As Control

Set ctlAct = Screen.ActiveControl

If IsNull(ctlAct.Value) = True Then Exit Function


ctlAct.Value = OnlyNumbers(ctlAct.Value)


If Len(ctlAct.Value) = 7 Then
' put in a default area code if the user does not...
ctlAct.Value = "780" & ctlAct.Value
End If


End Function

Public Function OnlyNumbers(myphone As String) As String

Dim i As Integer
Dim mych As String

OnlyNumbers = ""

If IsNull(myphone) = False Then

For i = 1 To Len(myphone)
mych = Mid$(myphone, i, 1)
If InStr("0123456789", mych) > 0 Then
OnlyNumbers = OnlyNumbers & mych
End If
Next i
End If


End Function

You then of course forget about using the input mask, but set the format for
the control as you suggest...

 

[John Vinson]

What I'd like to do is code the field to strip out any non-numeric
characters when the number is input.

Remove the Input Mask (it'll just get in the way too soon for any
other code to help); instead set the textbox's Format property
appropriately.

In a Module (basUtilities in my case, whatever name you like just so
it doesn't duplicate any existing Sub or Function name) put:

Public Function JustNumbers(strIn As String) As String
Dim iPos As Integer
Dim strOut As String
strOut = ""
For iPos = 1 to Len(strIn)
If IsNumeric(Mid(strIn, iPos, 1) Then
strOut = strOut & Mid(strIn, iPos, 1)
End If
Next iPos
End Function

In the control's AfterUpdate event put

Me!textboxname = JustNumbers(Me!textboxname)

John W. Vinson[MVP]