EASY Validation Rule

[Guest]

I have a combobox [CupholderID] where I will select the type of cupholder I
want. In my text box [Cupholder_qty] I want to use a Validation Rule where if
[CupholderID] = "None" then [Cupholder_qty] must be Zero, Otherwise It
doesn't matter what [Cupholder_qty] is

Thanks in advance
Travis

 

[Allen Browne]

Open your *table* in design view.
Open the Properties box (View menu.)
Set the Validation Rule in the Properties box to:
([CupHolderID] Is Null) OR ([CupHolderID] <> "None") OR ([Cupholder_qty]
= 0)

Since your validation rule involves multiple fields, you must use the rule
for the table (in the Properties box), not the rule for a field (in the
lower pane of table design.)

The rule can be satisified 3 ways:
a) If CupHolderID is null (left blank), it's satisifed.
b) If CupHolderID is a value other than "None", it's satisfied.
c) If neither of those apply, it is only satisfied if the qty is zero.

Note that if CupHolderID is a combo in your table, the actual value could be
something other than the text "None", even if that's what is displayed.

 

[Jamie Collins]

Open your *table* in design view.
Open the Properties box (View menu.)
Set the Validation Rule in the Properties box to:
([CupHolderID] Is Null) OR ([CupHolderID] <> "None") OR ([Cupholder_qty]
= 0)

There is a redundant test in your proposed Validation Rule, being:

([CupHolderID] Is Null)

If the column [CupHolderID] is required then the test for NULL is
obviously redundant; if it is nullable then the test is still
redundant, less obviously so.

According to the ANSI SQL-92 standard, this is how a Validation Rule
(CHECK constraint) is supposed to work:

<quote>

If the <table constraint> is a <check constraint definition>, then let
SC be the <search condition> immediately contained in the <check
constraint definition> and let T be the table name included in the
corresponding table constraint descriptor; the table constraint is not
satisfied if and only if

EXISTS ( SELECT * FROM T WHERE NOT ( SC ) )

is true.

</quote>

X3H2-92-154/DBL CBR-002 [ANSI SQL-92 Standard specification], 11.6
<table constraint definition>, section 3, P271.

Revising your proposed Validation Rule to

([CupHolderID] <> "None") OR ([Cupholder_qty] = 0)

means that a NULL value for [CupHolderID] would cause

[CupHolderID] <> "None"

to return NULL, the NULL would cause the search condition SC to be
NULL, which would cause the EXISTS to return false. Because a table
constraint is not satisfied if and only if the EXISTS is true, the
table constraint fails to be not satisfied. Another way of saying, 'The
table constraint fails to be not satisfied' is, 'The table constraint
is satisfied.'

There is a note to this effect on your website:

<quote>
Note: Data Definition Language (DDL) queries treat nulls differently
</quote>

Common Errors with Null by Allen Browne
http://allenbrowne.com/casu-12.html

Another way to think of this is, if a NULL value didn't exhibit this
behaviour in a Validation Rule you'd have explicitly test for NULL for
every nullable column and that would become tiresome.

Jamie.

--