Validation rule

[shaggles]

How can I write a validation rule that will require that
will require data in another field? My database has a
field to sign off on regulatory notices but the users want
to require that a comment be entered before signing off.
I tried typing =[Comments] Is Not Null in the
ValidationRule property for the sign off field but that
doesn't work.

 

[Allen Browne]

For comparison between fields, you must use the Validation Rule for the
*table*, not that of the fields.

1. Open the table in design view.

2. Open the Properties box (View menu).

3. Beside the Validation Rule in the Properties box (not the lower pane of
table design), enter something like this:
([Sign Off] = False) OR ([Comments] Is Not Null)

That assumes that Sign Off is a yes/no field. The rule can be satisfied in
two ways:
- if Sign Off is unchecked, the rule is satisifed;
- if Sign Off is checked, then the only other way the rule can be satisifed
is if Comments has some entry.

Make sure you also set the Allow Zero Length property of the Comments field
to No, so it cannot be a zero-length string either. You really should do
that for all Text, Memo, and Hyperlink fields.

 

[shaggles]

You're a genius. Thanks.

-----Original Message-----
For comparison between fields, you must use the Validation Rule for the
*table*, not that of the fields.

1. Open the table in design view.

2. Open the Properties box (View menu).

3. Beside the Validation Rule in the Properties box (not the lower pane of
table design), enter something like this:
([Sign Off] = False) OR ([Comments] Is Not Null)

That assumes that Sign Off is a yes/no field. The rule can be satisfied in
two ways:
- if Sign Off is unchecked, the rule is satisifed;
- if Sign Off is checked, then the only other way the rule can be satisifed
is if Comments has some entry.

Make sure you also set the Allow Zero Length property of the Comments field
to No, so it cannot be a zero-length string either. You really should do
that for all Text, Memo, and Hyperlink fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I write a validation rule that will require that
will require data in another field? My database has a
field to sign off on regulatory notices but the users want
to require that a comment be entered before signing off.
I tried typing =[Comments] Is Not Null in the
ValidationRule property for the sign off field but that
doesn't work.

.