eliminating undesired characters during input

[twas via AccessMonster.com]

How do I delete undesired characters from a text box and then check the
length of the resulting string when the user moves the focus to another
control? For example, if only numbers are desired in the output, the user
should be able to type
01-23-45
and the value for the text box when they moved the focus would be
012345
which would be OK with a length of 6 but
123456.7 would come out
1234567 which would be bad at length of 7

Thanks
twas

 

[Guest]

Hi twas,

If you want to be tricky, you could use a keypress event to check what
character was entered and ignore it if it's not what you want...

You might find it easier however, especially since you have other things to
check, to have an afterupdate function that checks that the length,
characters input etc conform to your rules that you wish to implement.

Of course, if you are wanting a maximum of six characters ever, setting the
field size to 6 would be a good start.

Hope this helps.

Damian.

 

[Graham Mandeno]

Hi twas

You could simply ignore any non-numeric key that is pressed (except
backspace) and ignore even numeric keys if the length is already 6:

Private Sub txtNumber_KeyPress(KeyAscii As Integer)
If (KeyAscii < vbKey0 Or KeyAscii > vbKey9 _
Or Len(txtNumber.Text) >= 6) _
And KeyAscii <> vbKeyBack Then KeyAscii = 0
End Sub

Alternatively, you could use an input mask, but I'm personally not in favour
of them.

 

[twas via AccessMonster.com]

thanks - that's close enough to what I need, probably. I actually need to
tell the user if they've entered the wrong number of characters -- too few is
as bad as too many.

Hi twas

You could simply ignore any non-numeric key that is pressed (except
backspace) and ignore even numeric keys if the length is already 6:

Private Sub txtNumber_KeyPress(KeyAscii As Integer)
If (KeyAscii < vbKey0 Or KeyAscii > vbKey9 _
Or Len(txtNumber.Text) >= 6) _
And KeyAscii <> vbKeyBack Then KeyAscii = 0
End Sub

Alternatively, you could use an input mask, but I'm personally not in favour
of them.

How do I delete undesired characters from a text box and then check the
length of the resulting string when the user moves the focus to another

[quoted text clipped - 9 lines]

Thanks
twas

 

[twas via AccessMonster.com]

Hi, Damian
using an afterupdate function sounds like it might work perfectly. That way,
the user could paste in a string that had all sorts of extra characters, and
I could eliminate those characters, check the resultant length, and provide
error messages if they typed the wrong number of characters.
To save the filtered string without the extra characters would I write a VBA
function that built a string with the desired characters, and save it back as
the value of the text box, and triggered a msgbox if the length were wrong?
thanks
twas

Hi twas,

If you want to be tricky, you could use a keypress event to check what
character was entered and ignore it if it's not what you want...

You might find it easier however, especially since you have other things to
check, to have an afterupdate function that checks that the length,
characters input etc conform to your rules that you wish to implement.

Of course, if you are wanting a maximum of six characters ever, setting the
field size to 6 would be a good start.

Hope this helps.

Damian.

How do I delete undesired characters from a text box and then check the
length of the resulting string when the user moves the focus to another

[quoted text clipped - 9 lines]

Thanks
twas

 

[J. Goddard]

Hi -

Instead of removing undesired characters, you can prevent them from
being entered at all by using the Input Mask property of the text box.

In your case, if the length of the text must be exactly 6, and only
digits, set the input mask to "000000"

If the length can be 1 to 6, use 099999 as the Input Mask.

HTH

John

 

[twas via AccessMonster.com]

I tried that, but it makes the user interface very unfriendly - the user
isn't allowed to try typing in extra characters, and I think the user can't
paste in a character string with extra characters. For example, they could
not paste 12.34.56 into the text box and have the result be accepted as a
valid 6 character input. In addition, I actually asked a simplified form of
the question; I actually need to filter out all but hexidecimal characters,
and I don't know how to tell an input mask that acceptable characters are 0-9,
A-F, a-f. In the real code, I expect to change a-f to A-F.

thanks
twas

Hi -

Instead of removing undesired characters, you can prevent them from
being entered at all by using the Input Mask property of the text box.

In your case, if the length of the text must be exactly 6, and only
digits, set the input mask to "000000"

If the length can be 1 to 6, use 099999 as the Input Mask.

HTH

John

How do I delete undesired characters from a text box and then check the
length of the resulting string when the user moves the focus to another

[quoted text clipped - 9 lines]

Thanks
twas

 

[J. Goddard]

Hi -

Here's a bit of code to try:

dim J as integer, strFixed as string, ch as string
strfixed = ""
for J = 1 to len(me![yourtextbox])
ch = mid(me![yourtextbox],J,1)
if instr("ABCDEF0123456789", ch) > 0 then strFixed = strfixed & ch
next J
strfixed=ucase(strfixed) ' Convert to upper case
'
' Don't update the text box if incorrect data was entered
'
if len(strfixed) <> 6 then
msgbox ....
else
me![yourtextbox] = strfixed
endif


Note that for the most part, when comparing text, "A"="a", "B"="b" ,
"CAT"="Cat", etc., so the instr function works for upper and lower case
data.

HTH

John

I tried that, but it makes the user interface very unfriendly - the user
isn't allowed to try typing in extra characters, and I think the user can't
paste in a character string with extra characters. For example, they could
not paste 12.34.56 into the text box and have the result be accepted as a
valid 6 character input. In addition, I actually asked a simplified form of
the question; I actually need to filter out all but hexidecimal characters,
and I don't know how to tell an input mask that acceptable characters are 0-9,
A-F, a-f. In the real code, I expect to change a-f to A-F.

thanks
twas

Hi -

Instead of removing undesired characters, you can prevent them from
being entered at all by using the Input Mask property of the text box.

In your case, if the length of the text must be exactly 6, and only
digits, set the input mask to "000000"

If the length can be 1 to 6, use 099999 as the Input Mask.

HTH

John

How do I delete undesired characters from a text box and then check the
length of the resulting string when the user moves the focus to another

[quoted text clipped - 9 lines]

Thanks
twas

 

[twas via AccessMonster.com]

Nice approach to the function; I hadn't thought of that approach. It might be
easier than the case statement I was thinking about.

I was thinking that if the user had typed the wrong number of characters, I
should pop up a msgbox that allowed cancelling the operation or fixing the
input (in the real world, the input has to be 16 characters, and it would be
all too easy to type 15 or 17 instead).

I'm not at all sure of which event(s) to use to trigger the filtering and the
length test, nor of how to allow the user to cancel the input or to modify
the existing character input.

thanks for the suggestion, and for the realization that the user needs to be
able correct the input without retyping it somehow...
twas

Hi -

Here's a bit of code to try:

dim J as integer, strFixed as string, ch as string
strfixed = ""
for J = 1 to len(me![yourtextbox])
ch = mid(me![yourtextbox],J,1)
if instr("ABCDEF0123456789", ch) > 0 then strFixed = strfixed & ch
next J
strfixed=ucase(strfixed) ' Convert to upper case
'
' Don't update the text box if incorrect data was entered
'
if len(strfixed) <> 6 then
msgbox ....
else
me![yourtextbox] = strfixed
endif

Note that for the most part, when comparing text, "A"="a", "B"="b" ,
"CAT"="Cat", etc., so the instr function works for upper and lower case
data.

HTH

John

I tried that, but it makes the user interface very unfriendly - the user
isn't allowed to try typing in extra characters, and I think the user can't

[quoted text clipped - 29 lines]

 

[Graham Mandeno]

This is precisely my objection to input masks - rather clumsy and not
user-friendly.

If you want to paste data into the field then the KeyPress idea won't help
either because it won't check the pasted data.

I think you'd be best to use BeforeUpdate to check for length and invalid
characters, and then use AfterUpdate to convert to uppercase and remove
unwanted (as opposed to invalid) characters.

It might help to know what this data actually is. I'm guessing MAC
addresses, or something similar.

In which case you probably want to do more than just ignore any
non-hex-digits and hope the length of what's left is 16. For example, the
following should probably be flagged as invalid, for several reasons, even
though it has exactly 16 hex-digits:

01-23w-5g-9f-&33b;cat5e-00

--

Graham Mandeno [Access MVP]
Auckland, New Zealand

I tried that, but it makes the user interface very unfriendly - the user
isn't allowed to try typing in extra characters, and I think the user
can't
paste in a character string with extra characters. For example, they could
not paste 12.34.56 into the text box and have the result be accepted as a
valid 6 character input. In addition, I actually asked a simplified form
of
the question; I actually need to filter out all but hexidecimal
characters,
and I don't know how to tell an input mask that acceptable characters are
0-9,
A-F, a-f. In the real code, I expect to change a-f to A-F.

thanks
twas

Hi -

Instead of removing undesired characters, you can prevent them from
being entered at all by using the Input Mask property of the text box.

In your case, if the length of the text must be exactly 6, and only
digits, set the input mask to "000000"

If the length can be 1 to 6, use 099999 as the Input Mask.

HTH

John

How do I delete undesired characters from a text box and then check the
length of the resulting string when the user moves the focus to another

[quoted text clipped - 9 lines]

Thanks
twas

 

[twas via AccessMonster.com]

Thanks for the suggestion. You were right about the real data, even though I
had the wrong length in one of the posts. The data is a MAC address, 12
hexidecimal characters, commonly written with periods or colons or dashes
separating groups of paired characters. Removing those separation characters
lets me verify that the data might be a valid MAC address, and keeps the data
in the database consistent.

You're also right about the process not being smart enough to detail all the
obviously invalid MAC addresses, like someone typing in cat-5e (which would
come out as ca5e after removing all but the hexidecimal characters). I think
it's enough just to ensure the input data is consistent and the right length;
there's no way to guess how dumb the users might be.

It sounds like all I really need to do is to process the data in the
BeforeUpdate event, and then I could use the same routine (assuming it set
the focus to the MAC address field) in some event (onDirty, perhaps) to check
that old data imported into the database had been entered correctly.

Is there a need to have an AfterUpdate event as well?

Is there a better approach to verifying the data in the database, and if not,
which event should I use to trigger the scan & length check?

thanks
twas

This is precisely my objection to input masks - rather clumsy and not
user-friendly.

If you want to paste data into the field then the KeyPress idea won't help
either because it won't check the pasted data.

I think you'd be best to use BeforeUpdate to check for length and invalid
characters, and then use AfterUpdate to convert to uppercase and remove
unwanted (as opposed to invalid) characters.

It might help to know what this data actually is. I'm guessing MAC
addresses, or something similar.

In which case you probably want to do more than just ignore any
non-hex-digits and hope the length of what's left is 16. For example, the
following should probably be flagged as invalid, for several reasons, even
though it has exactly 16 hex-digits:

01-23w-5g-9f-&33b;cat5e-00

I tried that, but it makes the user interface very unfriendly - the user
isn't allowed to try typing in extra characters, and I think the user

[quoted text clipped - 31 lines]

 

[Graham Mandeno]

Hi twas

The reason for having both BeforeUpdate and AfterUpdate is that you can't
change the data (e.g. eliminate characters) in the BeforeUpdate event, but
BeforeUpdate is the best place for validation because you can cancel the
event if there's a problem with the data.

I suggest you write a function to validate a MAC address and remove the
separators - something like this:

Public Function CleanMAC(sMAC As String) As String
Const cHexDigits = "0123456789ABCDEF"
Const cSeparators = "-:.,"
' Accept only strings in the form:
' hhshhshhshhshhshh
' or
' hhhhhhhhhhhh
' where h is a hex digit and s is a separator
' Remove separators if present
' Return null string if invalid
Dim ch As String, i As Integer
Dim fHasSeps As Boolean, sClean As String
fHasSeps = Len(sMAC) = 17
If Len(sMAC) <> 12 And Not fHasSeps Then Exit Function
For i = 1 To Len(sMAC)
ch = Mid(sMAC, i, 1)
If fHasSeps And (i Mod 3 = 0) Then
If InStr(cSeparators, ch) = 0 Then Exit Function
Else
If InStr(cHexDigits, ch) = 0 Then Exit Function
sClean = sClean & ch
End If
Next
CleanMAC = sClean
End Function

You can then call this in BeforeUpdate and store the result in a
module-level variable.

If the result is "" the MAC is invalid, so set Cancel = True.

Then, in AfterUpdate, copy the clean MAC from the module-level variable back
to the updated control.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the suggestion. You were right about the real data, even though
I
had the wrong length in one of the posts. The data is a MAC address, 12
hexidecimal characters, commonly written with periods or colons or dashes
separating groups of paired characters. Removing those separation
characters
lets me verify that the data might be a valid MAC address, and keeps the
data
in the database consistent.

You're also right about the process not being smart enough to detail all
the
obviously invalid MAC addresses, like someone typing in cat-5e (which
would
come out as ca5e after removing all but the hexidecimal characters). I
think
it's enough just to ensure the input data is consistent and the right
length;
there's no way to guess how dumb the users might be.

It sounds like all I really need to do is to process the data in the
BeforeUpdate event, and then I could use the same routine (assuming it set
the focus to the MAC address field) in some event (onDirty, perhaps) to
check
that old data imported into the database had been entered correctly.

Is there a need to have an AfterUpdate event as well?

Is there a better approach to verifying the data in the database, and if
not,
which event should I use to trigger the scan & length check?

thanks
twas

This is precisely my objection to input masks - rather clumsy and not
user-friendly.

If you want to paste data into the field then the KeyPress idea won't help
either because it won't check the pasted data.

I think you'd be best to use BeforeUpdate to check for length and invalid
characters, and then use AfterUpdate to convert to uppercase and remove
unwanted (as opposed to invalid) characters.

It might help to know what this data actually is. I'm guessing MAC
addresses, or something similar.

In which case you probably want to do more than just ignore any
non-hex-digits and hope the length of what's left is 16. For example, the
following should probably be flagged as invalid, for several reasons, even
though it has exactly 16 hex-digits:

01-23w-5g-9f-&33b;cat5e-00

I tried that, but it makes the user interface very unfriendly - the user
isn't allowed to try typing in extra characters, and I think the user

[quoted text clipped - 31 lines]

Thanks
twas