table level validation

[Micky]

I have the following expression in a tables validation
rule.
([UNITS PAID FOREMAN]>0 And [INVOICE UNIT PRICE]>0) Or
([UNITS PAID FOREMAN] Is Null And [INVOICE UNIT PRICE] Is
Null) Or ([UNITS PAID FOREMAN]=0 And [INVOICE UNIT PRICE]
=0)
All data is entered into the table from a form. It works
for the most part, except for the Nulls. This will only
make sure things are in tact if there is a value in both
columns. If you leave one null then it says it is OK
even though the other has a value. For other reasons I
really do not want to not allow a null value. Any way to
validate this?

Thanks in advance?

 

[Brian Camire]

The problem is that your validation rule is evaluating to Null when either
of "UNITS PAID FOREMAN" or "INVOICE UNIT PRICE" is Null.

This happens because of the following way Access handles Nulls in logical
expressions:

Null And True = Null
Null And False = False
Null And Null = Null

Null Or True = True
Null Or False = Null
Null Or Null = Null

when "UNITS PAID FOREMAN" is Null, both

[UNITS PAID FOREMAN]>0

and

[UNITS PAID FOREMAN]=0

evaulate to Null.

If "INVOICE UNIT PRICE" is greater than zero

[UNITS PAID FOREMAN]>0 And [INVOICE UNIT PRICE]>0

evalutates to

Null AND True = Null

[UNITS PAID FOREMAN] Is Null And [INVOICE UNIT PRICE] Is Null

evaluates to False and

[UNITS PAID FOREMAN]=0 And [INVOICE UNIT PRICE]=0

evaluates to

Null And False = False

so the whole validation rule evaulates to:

Null Or False Or False = Null

which is not False, so the validation rule is not violated.

Similarly, if "INVOICE UNIT PRICE" is equal to zero, you get:

(Null And False) Or False Or (Null And True) = False Or False Or Null = Null

which is not False, so the validation rule is not violated.

However, if "INVOICE UNIT PRICE" is less than zero, you get:

(Null And False) Or False Or (Null And False) = False Or False Or False =
False

so the validation rule is violated.

You get the same thing if you "INVOICE UNIT PRICE" is Null and "UNITS PAID
FOREMAN" is not.

In any case, you might try a validation rule like this instead:

Nz([UNITS PAID FOREMAN]>0 And [INVOICE UNIT PRICE]>0,False) Or ([UNITS PAID
FOREMAN] Is Null And [INVOICE UNIT PRICE] Is Null) Or Nz([UNITS PAID
FOREMAN]=0 And [INVOICE UNIT PRICE]=0,False)