Microsoft KB Archive/209106

= ACC2000: Table Field Validation Rule Causes Error =

Article ID: 209106

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.



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.</li>  Create a new table called Table1 with the following properties: <pre class="fixed_text">  Table: Table1 -- Field Name: ABC Data Type: Date/Time Format: ShortDate Field Name: DEF Data Type: Date/Time Format: ShortDate </li> Save the table (do not define a primary key).</li> On the View menu, click Properties.</li> Set the ValidationRule property in the Table Properties dialog box to:

[ABC]<[DEF]

</li> Close the Table Properties dialog box, and then save the table (do not define a primary key).</li>  Open Table1 in Datasheet view and type the following record: <pre class="fixed_text">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. </li></ol>

<div class="references_section">