Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 209106 - Last Review: June 30, 2004 - Revision: 2.0
ACC2000: Table Field Validation Rule Causes Error
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).
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.
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
- 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
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.
APPLIES TO
- Microsoft Access 2000 Standard Edition
| kberrmsg kbprb kbusage KB209106 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate