IF Statement in Validation Rule

[MT DOJ Help Desk]

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

 

[Allen Browne]

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)

 

[Peter Johal]

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

 

[MT DOJ Help Desk]

The field is not mandatory.

--Tom

 

[MT DOJ Help Desk]

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

 

[Allen Browne]

You could try removing the Input Mask.