Limit input characters (Access97)

Topic

New Reply

Is there a way to limit the characters entered into a field to
just Capital A to Z and an _
?
I did a search here and either didn’t use the right phrase or no one asked before.
Thanks,
Scott

Reply | Quote

Replies

Could you please define what is a field. Are you entering data directly into a table, on a form, in a combo box, etc??

Reply | Quote

Gary,
I’m entering the info into a field on a form then does some VBA processing
Thanks

Reply | Quote

Do you mean like an input mask?

If so, these are from Help:

L = Letter (A to Z, entry required).
? = Letter (A to Z, entry optional).

You can enter the appropriate mask in the textbox’s Input Mask property.

Reply | Quote

Cecilia,
Not an Input mask. I want to restrict the user from entering other than A to Z or an underscore.
I don’t want them to be able to enter a * ,& , %, any other punctuation, or a space.
The input is used in some code to generate some tables and do some other processing and the non alphabet characters cause errors.
Scott

Reply | Quote

Wouldn’t an input mask restrict what the user can enter?

Do you mean a validation rule then? Are you looking for code that checks the last character entered and tests to see if it’s a valid entry?

Sorry, sign me
~~Confused

Reply | Quote

Looking for code in the BeforeUpdate event to check for the conditions listed above.
I could do it with a select case stmt but I would have to have it check for all the characters in the string
except the ones that I wanted. Or use instr to search for everything I don’t want.
I was looking for an easier way.
Maybe I’m not explaining myself clear enough. If input A to Z or _, msgbox “Use only A to Z or _”

Reply | Quote

.. I was having the same ideas. I am sure there are better ways to do this.

Reply | Quote

Okay, I realize that you don’t want an input mask, but now I’m asking you….What does this code do that an input mask doesn’t?

For example, if I use as my input mask LLLLL (or ?????), then the user must enter (at least) five letters. (AAAAA will require (aaaaa is entry optional) five letters or numbers, excluding symbols.) Isn’t that what you’re after? To prevent the user from entering anything but exactly what you want? What is the benefit to doing this in code?

Just curious,

Cecilia

Reply | Quote

Cecilia,
I don’t think a input mask can accept an underscore( _ )
If they type in ATLANTA GA or type in HOME DEPOT in the field, I want to reject it.
They should type in ATLANTA_GA or HOME_DEPOT as the instructions say.
The longest the string of characters would be is about 20.
Scott

Reply | Quote

How about checking the characters by their chr codes?

Numbers start at chr(48) through chr(57), letters are chr(65) through chr(90) and chr(97) through (122), underscore is chr(94). You could either validate by going through each of these in a loop or use the before update to check the string.

Just an idea to make up for my dumbness earlier today

Cecilia

Reply | Quote

Depending on how much data is going to be typed into the field, you can use the OnChange event to test every character typed into the control to validate it against a set of criteria. OnChange happens before BeforeUpdate and AfterUpdate, and it allows you to validate as you go along.

Otherwise, you can use the InStr function in the BeforeUpdate event of the control to test and see if a string contains a particular character, either using the Ascii value of the character or just using a text string like ” ” for a space.

Still another option is to step through the characters one at a time, testing each against the range of valid characters with a Select Case. You can also use Ucase() to force the entered value to upper case.

Reply | Quote

You might use the KeyPress event of the text box:

Private Sub txtMyField_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case 8, 65 To 90, 95
‘ Backspace, upper case, underscore OK
Case 97 To 122
‘ Convert lower case to upper case
KeyAscii = KeyAscii – 32
Case Else
‘ Ignore the rest
KeyAscii = 0
End Select
End Sub

Reply | Quote

Thanks Hans,
Here is another * to ad to your 4 Star rating
Scott

Reply | Quote

Is sounds as if you want to keep the characters entered from A to Z or an underscore even if the length of the field changes.

I would suggest that on the after update or on change event of the parameter entered on the form that you launch a function that reviews the data for “invalid” characters. If the characters are invalid, stop the VBA processing and provide a message box to the user to re-insert the characters.

A function similar to this may work.

Function SubC1(strMyString As String) As String
If InStr(strMyString, “-“) > 0 Then MsgBox “ERROR” ‘Check for invalid character(s)
SubC1 = strMyString

End Function

You will need to add additional invalid parameters. You could also loop through the text to check it as well.

Hope this helps or gives you some ideas. I wonder if you could put this code on the validation line of the form where the data is being entered??

Reply | Quote