Is there a smarter way...

G

Guest

I am inputting phone numbers using voice recognition. VR sometimes places a
"-" in a number when it thinks it is a phone number (which it is), but I just
want the raw number, such as 5556666 instead of 555-6666. It sometimes hears
sounds and places words in the field as well, such as 555and6666. is there a
smarter way to do this:

Private Sub INPUT_NUMBER_Exit(Cancel As Integer)
'Numbers only
If Not IsNull(INPUT_NUMBER) Then
INPUT_NUMBER = Replace(INPUT_NUMBER, "-", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, " ", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "a", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "b", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "c", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "d", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "e", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "f", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "g", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "h", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "i", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "j", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "k", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "l", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "m", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "n", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "o", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "p", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "q", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "r", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "s", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "t", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "u", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "v", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "w", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "x", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "y", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "z", "")
End If
End Sub

When inputting, I do not want the input to be interrupted, just cleanedup
before updating or at least cleanedup upon exiting. I tried "BeforeUpdate"
but received an error saying it did not like this done BeforeUpdate or
validation rule was stopping it (I have no validation rule).

Thanks.
 
D

Dirk Goldgar

Rod said:
I am inputting phone numbers using voice recognition. VR sometimes
places a "-" in a number when it thinks it is a phone number (which
it is), but I just want the raw number, such as 5556666 instead of
555-6666. It sometimes hears sounds and places words in the field as
well, such as 555and6666. is there a smarter way to do this:

Private Sub INPUT_NUMBER_Exit(Cancel As Integer)
'Numbers only
If Not IsNull(INPUT_NUMBER) Then
INPUT_NUMBER = Replace(INPUT_NUMBER, "-", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, " ", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "a", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "b", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "c", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "d", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "e", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "f", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "g", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "h", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "i", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "j", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "k", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "l", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "m", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "n", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "o", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "p", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "q", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "r", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "s", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "t", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "u", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "v", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "w", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "x", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "y", "")
INPUT_NUMBER = Replace(INPUT_NUMBER, "z", "")
End If
End Sub

When inputting, I do not want the input to be interrupted, just
cleanedup before updating or at least cleanedup upon exiting. I
tried "BeforeUpdate" but received an error saying it did not like
this done BeforeUpdate or validation rule was stopping it (I have no
validation rule).
Click to expand...

The control's AfterUpdate event would be a better place to run the code.
That way it wouldn't run when the focus passes through the control
without changing anything.

You might find this function useful:

'----- start of function code -----
Function StripAllButDigits(varOldNumber As Variant) As String

'Removes any non-numeric characters in a string

Dim I As Integer
Dim intLength As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String

strOldNumber = varOldNumber & vbNullString

intLength = Len(strOldNumber)

strNewNumber = vbNullString

For I = 1 To intLength
strThisCharacter = Mid(strOldNumber, I, 1)
If strThisCharacter >= "0" And strThisCharacter <= "9" Then
strNewNumber = strNewNumber & strThisCharacter
End If
Next I

StripAllButDigits = strNewNumber

End Function
'----- end of function code -----

Then you could call it in the text box's AfterUpdate event:

'----- start of event code -----
Private Sub INPUT_NUMBER_AfterUpdate()

With Me!INPUT_NUMBER
.Value = StripAllButDigits(.Value)
End With

End Sub
'----- end of event code -----
 
G

Guest

Excellent, Dirk. Thanks Much!

Dirk Goldgar said:
The control's AfterUpdate event would be a better place to run the code.
That way it wouldn't run when the focus passes through the control
without changing anything.

You might find this function useful:

'----- start of function code -----
Function StripAllButDigits(varOldNumber As Variant) As String

'Removes any non-numeric characters in a string

Dim I As Integer
Dim intLength As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String

strOldNumber = varOldNumber & vbNullString

intLength = Len(strOldNumber)

strNewNumber = vbNullString

For I = 1 To intLength
strThisCharacter = Mid(strOldNumber, I, 1)
If strThisCharacter >= "0" And strThisCharacter <= "9" Then
strNewNumber = strNewNumber & strThisCharacter
End If
Next I

StripAllButDigits = strNewNumber

End Function
'----- end of function code -----

Then you could call it in the text box's AfterUpdate event:

'----- start of event code -----
Private Sub INPUT_NUMBER_AfterUpdate()

With Me!INPUT_NUMBER
.Value = StripAllButDigits(.Value)
End With

End Sub
'----- end of event code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simple Replace 3
Remove Alpha Characters 39
VBA to find filename and replace 2
Is a macro possible here? 4
Bankers! 7
Find and remove blanks 1
Replace email string in contacts MS Outlook 0
Automated google search 5

Top