Table Validation rule

[DP7]

Hi I am somewhat new to access. In my table I would like to set up a
validation RULE for one of the dates fields in my table. I want to know what
code I need to write to tell access that my OrderEntryClose field must be
greater than the order received date field.
Thanks

 

[John W. Vinson]

Hi I am somewhat new to access. In my table I would like to set up a
validation RULE for one of the dates fields in my table. I want to know what
code I need to write to tell access that my OrderEntryClose field must be
greater than the order received date field.
Thanks

It's probably easier to do this in the BeforeUpdate event of a Form, but you
can set the Table Validation rule to

[OrderEntryClose] > [OrderReceived] OR [OrderEntryClose] IS NULL

Omit from the OR on if the field is required. Put a meaningful message in the
ValidationText field which will be shown to the user if they goof.

John W. Vinson [MVP]

 

[Jamie Collins]

you
can set the Table Validation rule to

[OrderEntryClose] > [OrderReceived] OR [OrderEntryClose] IS NULL

Omit from the OR on if the field is required.

My brain finds it easier to read the dates in chrono order left-to-
right e.g.

OrderReceived < OrderEntryClose

FWIW as from Jet 4.0 (Access 2000 and above) there is no longer any
need to explicitly test for the NULL value in the above. Back in the
Jet 3.51 days (Access97 and earlier) it was the case that aValidation
Rule had to evaluate to TRUE to allow the update. Jet 4.0 was revised
to make it more compliant with the SQL-92 standard, which states
specifies that

"A table check constraint is satisfied if and only if the specified
<search condition> is not false"

The context is that (truly) table-level CHECK constraints were
introduced into Jet 4.0 and a Jet Table Validation Rule (actually row-
level!) is analogous to a SQL-92 table CHECK constraint with no
subquery. Remember that the SQL-language has three-value logic, TRUE,
FALSE and UNKNOWN. A search condition such as DATE() < NULL will
evaluate to UNKNOWN and, because it is not FALSE, the Validation Rule
is satisfied.

This is slightly counter-intuitive in that a search condition in a
WHERE clause im SQL DML (e.g. a query) which evaluates to UNKNOWN will
cause that rows to be *removed* from the resultset. The way I think of
it is: if the NULL value means that the value is not yet known then we
cannot know *now* whether the forthcoming value will or will not
satisfy the rule, therefore let's allow the NULL value placeholder
and evaluate the rule again when the NULL value is replaced by the
actual value.

It's a very practical stance, when you think about it. In the Jet 3.51
days you would have to 'escape' all the nullable columns by adding a
test for the NULL value. It's easier to defer null checking to the
column's own NOT NULL clause!

Bottom line: your test for NULL is probably redundant even when the
fields are not required

Jamie.

--