Field Validation Rule to limit input to printable ASCII characters ( 32 - 126 ).

I can't seem to come up with the correct syntax for a text field Validation Rule to limit user input to the ASCII character range 32 - 126.

I've tried:  ">=Chr$(32) and <=Chr$(126)"
                ">=(Chr$(32)) and <=(Chr$(126))"
                "Between (Chr$(32)) And (Chr$(126))"

Any help would be appreciated.
colin4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
your between should work

Between chr$(32) and chr$(126)

ensure you have entered validation text

this kicks in on exit of field
0
shanesuebsahakarnCommented:
I asume that you want the user to be able to input multiple characters, but limit each one to between the specified character range. If so, you need a custom function like this:

Function IsAlphanumeric(strIn As String) As Boolean
Dim I As Long

IsAlphanumeric=True
For I=1 To Len(strIn)
   If Asc(Mid$(strIn,I,1))<32 Or Asc(Mid$(strIn,I,1))>126 Then
      IsAlphanumeric=False
      Exit For
   End If
Next
End Function

Then set the validation rule as:
IsAlphanumeric(Form!MyTextbox)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
This is a problem. You can't use VB inside a validation rule, because they are evaluated by the JetEngine alone (e.g. when you open the database from C++).

Validation Rule, for a Text(1): Between ' ' And '~'
But you would have to find a way to tell the JetEngine to use ASCII sort for that expression...

If it's not possible, or for more characters, you will have to move that rule from the data model to the procedural model (i.e. test during user input, during import, before using the data, etc...) Then you can use VB (slight correction of Shane's function):

Function IsAscii7bit(varString) As Boolean
Dim I As Long

IsAscii7bit=True
If IsNull(varString) Then Exit Function
For I=1 To Len(varString)
   Select Case AscW(Mid$(varString,I,1))
      Case 32 To 126
         ' accepted
      Case Else
         IsAscii7bit=False
         Exit For
   End Case
Next
End Function

The main point here is "AscW()" instead of "Asc()", or you will miss many Unicode characters... For example:

    ? Asc( ChrW(1000) )
    63

Good Luck :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shanesuebsahakarnCommented:
You can so long as the validation rule is at form level rather than table level - I was assuming that this was the case.
0
shanesuebsahakarnCommented:
Well, control level anyway, but you know what I meant.
0
harfangCommented:
It seems I was assuming a *table* field validation rule. On a form, the function can be used to validate BeforeUpdate of the field's control, or you can limit user input in the control:

Private Sub Text0_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 32 To 126, vbKeyBack
            ' accept
        Case Else
            KeyAscii = 0
    End Select
End Sub

This prevents also TAB, CR, LF, etc, of course.

Cheers!
0
colin4Author Commented:
Thanks for all of the feedback, but with so many responses, I'm a bit overwhelmed and decided to start at the top.

I'm unable to get shanesuebsahakarn's solution to work.  I created a Public Function exactly as shown above (IsAlphaNumeric) and added the following to the text box Validation Rule field "=IsAlphaNumeric([Form]![AU_Coverage_Indications_Text])" (Note: MS Access forced the "=" sign into the Validation Rule)

Now, when I type an "a" into the text box and tab (or click) to the next field, the Validation Text message appears indicating a violation of the rule.

Is there something I missed ?

Also, the data-entry staff could do a cut-n-paste into the text box which could insert invalid characters.  So the check should be done based on the entire field before the contents of the field are accepted.

Thanks for your support.
0
shanesuebsahakarnCommented:
Sorry, try setting the rule to:
IsAlphaNumeric([Form]![AU_Coverage_Indications_Text])=True
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.