• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1112
  • Last Modified:

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.
0
colin4
Asked:
colin4
1 Solution
 
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
 
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now