IF Statement in Validation Rule

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MT DOJ Help Desk - 06 Feb 2004 08:58 GMT

Access 97

I have a table that has one field that does not always need to contain a
value, but when it does contain a value I would like to have a validation
rule to make sure the value is appropriate for the field. I tried putting a
validation rule on the field, but when I try to enter a record without a
value in that field, Access won't let me move to the next record.

Basically, what I would like is for the field to be validated when it
contains a value, but not when the field is left empty. Is it is possible
to have an IF statement in a validation rule, or to set the validation rule
refer to a macro?

--Tom

Reply to this Message

Allen Browne - 06 Feb 2004 10:58 GMT

Just use Is Null combined with your other condition via an OR operator.

For example, if you want to insist that a field is between 1900 and 2199 if
there is an entry, use a Validation Rule of:
(Is Null) OR (Between 1900 And 2199)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Access 97
>
[quoted text clipped - 8 lines]
> to have an IF statement in a validation rule, or to set the validation rule
> refer to a macro?

Reply to this Message

MT DOJ Help Desk - 07 Feb 2004 05:47 GMT

I didn't think about using IS NULL in conjunction with OR, but it does make
sense. I gave your suggestion a try. However, when I tested it by entering
a value that did not conform to the validation rule, I got an error message
saying that the value is not appropriate for the input mask, as opposed to
the message I specified for when the validation rule is violated. So it
looks like Access 97 is catching the bad data because of the input mask, and
not because of the violation of the validation rule. Any idea why it behave
in this manner?

BTW, I put the validation rule in the field properties of the *table*, and
not the field properties of the *form*.

--Tom

> Just use Is Null combined with your other condition via an OR operator.
>
[quoted text clipped - 21 lines]
> rule
> > refer to a macro?

Reply to this Message

Allen Browne - 07 Feb 2004 10:47 GMT

You could try removing the Input Mask.

> I didn't think about using IS NULL in conjunction with OR, but it does make
> sense. I gave your suggestion a try. However, when I tested it by entering
[quoted text clipped - 7 lines]
> BTW, I put the validation rule in the field properties of the *table*, and
> not the field properties of the *form*.

Reply to this Message

Peter Johal - 06 Feb 2004 11:01 GMT

Tom

Did you make the field mandatory? Access won't let you leave the field if it is mandatory and empty

Make the field optional and try something like: (field IS NULL) or (field > 100) as a validation rule

Peter

Reply to this Message

MT DOJ Help Desk - 07 Feb 2004 05:37 GMT

The field is not mandatory.

--Tom

> Tom,
>
[quoted text clipped - 4 lines]
>
> Peter

Reply to this Message