ACC2000: Table Field Validation Rule Causes Error

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

This behavior occurs because in Microsoft Access, 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. In Access, validation rules are enforced by the Microsoft Access Jet database engine.

To get the validation rule that 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.

Steps to Reproduce Behavior

1. Start Microsoft Access and create a new database or open an existing one.
2. Create a new table called Table1 with the following properties:

      Table: Table1
     --------------------------
     Field Name: ABC
        Data Type: Date/Time
        Format: ShortDate
     Field Name: DEF
        Data Type: Date/Time
        Format: ShortDate
        ValidationRule: >[ABC]
   					

3. Save the table (do not define a primary key).
   
   Note that you receive the error message stated above.

Steps to Create a Validation Rule at the Table Level

1. Start Microsoft Access and create a new database or open an existing one.
2. Create a new table called Table1 with the following properties:

      Table: Table1
     --------------------------
     Field Name: ABC
        Data Type: Date/Time
        Format: ShortDate
     Field Name: DEF
        Data Type: Date/Time
        Format: ShortDate
   					

3. Save the table (do not define a primary key).
4. On the View menu, click Properties.
5. Set the ValidationRule property in the Table Properties dialog box to:
   [ABC]<[DEF]
6. Close the Table Properties dialog box, and then save the table (do not define a primary key).
7. Open Table1 in Datasheet view and type the following record:

   ABC        DEF
   -----------------
   1/10/99    1/9/99
   					

   Notice that you receive an error message stating that the values entered are prohibited by the validation rule.

For more information about the ValidationRule property, click Microsoft Access Help on the Help menu, type validationrule, validationtext properties in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.