Validation Rules for Multi Columns Comparison

  • Thread starter Thread starter Winnie
  • Start date Start date
W

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
 
Please help if you know any other way to accomplish the
job I want to do.
Click to expand...

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.
 
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
 
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.
 
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.
Click to expand...

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".
 
Back
Top