ACC2000: Table Field Validation Rule Causes Error (209106)

Microsoft Access 2000

This article was previously published under Q209106

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

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

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:

Invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint.

CAUSE

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.

RESOLUTION

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.

MORE INFORMATION

Steps to Reproduce Behavior

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

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]

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

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

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

Save the table (do not define a primary key).
On the View menu, click Properties.
Set the ValidationRule property in the Table Properties dialog box to:
[ABC]<[DEF]
Close the Table Properties dialog box, and then save the table (do not define a primary key).
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.

REFERENCES

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.

Modification Type:

Major

Last Reviewed:

6/30/2004

Keywords:

kberrmsg kbprb kbusage KB209106