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

1. Start Microsoft Access and create a new database or open an existing one.



2. Create a new table.



3. Add two text fields named ABC and DEF to the table.



4. Set the ValidationRule property of the DEF control to:
   

   
         <[ABC] 




5. Save the table (do not define a primary key). Note that you receive one of the error messages stated above.

Keywords          : kberrmsg kbusage TblDsign 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: April 3, 1999