ACC: Validation Rules Evaluate Expressions in Unexpected Ways

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Microsoft Access evaluates expressions with logical operators differently than you expect. For example, if you enter zero in a field with the validation rule set to "=true and not true," you may receive the following error message:

   The value you entered is prohibited by the validation rule set for
   this field.

CAUSE

Microsoft Access evaluates expressions in parts, and compares each part to the value in the field being validated. The expression "=true and not true" is evaluated as:

   Field=True AND Field<>True

RESOLUTION

Use parentheses in the expression to change the way the expression is evaluated. The correct syntax for the example above is:

   =(True AND NOT True)

   Field=(True AND NOT True)

MORE INFORMATION

Steps to Reproduce Behavior

1. Open a new, blank form in Design view.

2. Add a text box with the following validation rule to the form:

         Text box: Field1
            Validation rule: =True and not True
   
   

3. View the form in Form view.

4. Enter a zero in the text box, and then press ENTER. Note that you receive the error message mentioned earlier in this article.