Validation rule involving another control

[Joel Parker]

I want to write a validation rule ensuring that the number in Field2 is
greater than the number in Field1. The problem is, both can be null.

How
do I write the rule so that Field1 can be null? Simply writing ">[Field1]
Or Is Null" doesn't seem to be working.

Thanks,
Joel

 

[Jeff Boyce]

Joel

You didn't mention where you are trying to do this, so I'll assume from the
'group you posted in that it is in a control's properties (you use
"controls" on a form to hold the values of "fields" on a table).

Validation between controls/fields requires checking values using event
procedures. A good spot to do this is in the Before Update event.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[banem2]

I want to write a validation rule ensuring that the number in Field2 is
greater than the number in Field1. The problem is, both can be null.

How
do I write the rule so that Field1 can be null? Simply writing ">[Field1]
Or Is Null" doesn't seem to be working.

Thanks,
Joel

Hi,

You can try with:

"> NZ(Field1, 0)"

This will make Null value as 0 so you can compare it with number in
Field2.

Regards,
Branislav Mihaljev

 

[Allen Browne]

In table design, open the Properties box (View menu.)

Set the Validation Rule in the Properties box to:
([Field1] Is Null) OR ([Field2] Is Null) OR ([Field2] > [Field1])

Don't use the validation rule in the lower pane of table design: that one
applies to one field only, whereas the one in the Properties box is for the
table (record-level validation.)

The rule can be satisfied 3 ways:
- If Field1 is Null, that's enough.
- If Field2 is Null, that's enough.
- Otherwise the only way it is satisifed is if Field2 is greater than
Field1.