Validation Rules for Multi Columns Comparison

[Winnie]

I have several related date columns like DateReceived,
DueDate, CompDate, QtrReceived, QtrDue, CompAtr, etc. I
tries to set some validation rules in order to avoid
typing (data entry) errors for the following columns:

DueDate: [DueDate]>=[DateReceived]
CompDate: [CompDate]>=[DueDate]
QtrReceived: DatePart("q", [DateReceived])
QtrDue: DatePart("q", [DueDate])
CompQtr: DatePart("q", [CompDate])

Since the validation rules set related to other columns,
I got the error message of "Invalid SQL syntax-cannot use
multiple columns in a column-level CHECK constraint."

Please help if you know any other way to accomplish the
job I want to do.

Thanks,

Winnie

 

[John Vinson]

Please help if you know any other way to accomplish the
job I want to do.

Use the Table Validation property of the table itself. This lets you
reference any field in the table; you will need to construct a single
long (sometimes complex) yes/no expression. If it evaluates to FALSE
the record will not be saved and the validation message will be
displayed.

It's often easier to enforce the use of a Form to do data entry, and
put code in the Form's before-update event.

 

[Winnie]

Hi John,

Thank you for the quick reply. How come I got only the
first validation rule shown after I saved all the v rules
for each field with a "," between each rule? Can you show
me an example if you have to put multipul v rules for
multiple fields?

Thanks for help.

Winnie

 

[Duane Hookom]

Try place " AND " between the individual expressions.

 

[Duane Hookom]

I just noticed that you are saving values that can be calculated
QtrReceived: DatePart("q", [DateReceived])
QtrDue: DatePart("q", [DueDate])
CompQtr: DatePart("q", [CompDate])
There is absolutely no reason to store values that can be easily calculated.

 

[John Vinson]

I just want to block any data entry errors. Sometimes
people type 0 or 5 or anything else for quarters. I'd
like to have those quarter fields automatically filled in
between number 1 and 4 based on the date entered.

Then DON'T STORE THE QUARTER IN YOUR TABLE AT ALL. It should not be
typed; it should not be in your table *period*. Since it can be
calculated whenever it's needed from the date, just *calculate it
whenever it's needed* - in a Query, or by putting a textbox on your
form or report bound to the date with a Format property of "q".