ACC: Unknown Reference or Function in Validation Expression

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

When you try to save a table that has a field name, control name, or user- defined function in a validation rule or default value, you may receive the following error messages:

In Microsoft Access 97 you receive the following message:

   The database engine does not recognize either the field '<field>' in a
   validation expression, or the default value in table '<Table>'.

   Unknown or invalid reference '<field>' in validation expression or
   default value in table '<Table>'.

   -or-

   Unknown function '<function>' in validation expression or default value
   in '<Table>'.

CAUSE

Validation rules are enforced by the Microsoft Access Jet database engine in Microsoft Access 2.0, 7.0 and 97. There are some rules allowed in Microsoft Access versions 1.0 and 1.1 that are not allowed at the field level in Microsoft Access 2.0, 7.0 and 97. In Microsoft Access 2.0, 7.0 and 97, validation rules at the field level cannot refer to other fields (whether in the current table or another table), controls on a form, or user-defined functions.

RESOLUTION

To get the validation rule you want, create the rule at the table level instead of the field level. Note that validation rules on a form can refer to other controls on the form.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior