requiring two fields to have non-null values

[Paul Ponzelli]

I have two fields in one of my tables that should only be populated if
they're both populated. If either one is null, they must both be null. The
fields are named "Action" and "Approval."

The form used to enter the data displays both fields at all times, so there
is no restriction on which field will be filled in first. I'm thinking that
if there is a way to set the validation rule at table level, I can let both
form controls remain visible at all times, and avoid the need to hide and
display one of the controls in the form's On Current event.

What is the best way to require that if users enter data in either one of
these fields, they must enter data in the other field before the record can
be saved?

Thanks in advance,

Paul

 

[Rick Brandt]

I have two fields in one of my tables that should only be populated if
they're both populated. If either one is null, they must both be
null. The fields are named "Action" and "Approval."

The form used to enter the data displays both fields at all times, so
there is no restriction on which field will be filled in first. I'm
thinking that if there is a way to set the validation rule at table
level, I can let both form controls remain visible at all times, and
avoid the need to hide and display one of the controls in the form's
On Current event.
What is the best way to require that if users enter data in either
one of these fields, they must enter data in the other field before
the record can be saved?

Thanks in advance,

Paul

Use a Table-Level validation rule like...

(IsNull([Field1])+IsNull([Field2]))<>-1

 

[Douglas J. Steele]

You can put code into the form's BeforeUpdate event to check the two fields
and cancel the update if they're not okay. However, I have to question your
design. Having fields whose values are dependent on one another doesn't
sound like a properly normalized table.

 

[Rick Brandt]

You can put code into the form's BeforeUpdate event to check the two
fields and cancel the update if they're not okay. However, I have to
question your design. Having fields whose values are dependent on one
another doesn't sound like a properly normalized table.

I disagree. In this case the values are not dependent on each other, only the
requirement to have a value is.

It is quite common for instance with Question/Answer applications to require
that an [Explanation] or [Details] field be filled out when certain responses
are filled in, but not when others are. A good example is a medical database...

Are you currently taking any medication? ( ) Yes ( ) No

If "Yes" please list ____________

 

[Paul Ponzelli]

Thanks for your reply, Rick.

Well, I entered the expression you suggested in the Validation Rule property
of the table fields using the actual field names:

(IsNull([Approval])+IsNull([Action]))<>-1

but Access gave me the following error message when I tried to leave Table
Design View:

"Invalid SQL syntax - cannot use multiple columns in a column-level CHECK
constraint.

Any idea how to overcome this problem?

I have two fields in one of my tables that should only be populated if
they're both populated. If either one is null, they must both be
null. The fields are named "Action" and "Approval."

The form used to enter the data displays both fields at all times, so
there is no restriction on which field will be filled in first. I'm
thinking that if there is a way to set the validation rule at table
level, I can let both form controls remain visible at all times, and
avoid the need to hide and display one of the controls in the form's
On Current event.
What is the best way to require that if users enter data in either
one of these fields, they must enter data in the other field before
the record can be saved?

Thanks in advance,

Paul

Use a Table-Level validation rule like...

(IsNull([Field1])+IsNull([Field2]))<>-1

 

[Paul Ponzelli]

Thanks for your comment about this, Doug.

In my table, we're tracking license applications, and one field records
whether the application is Approved or Denied, and the other records the
date it was Approved or Denied.

We need the information in both these fields, and we don't want one without
the other, so the logical justification to have this in a normalized table
is similar to what Rick described in the medical questionnaire.

Paul

 

[Rick Brandt]

Thanks for your reply, Rick.

Well, I entered the expression you suggested in the Validation Rule
property of the table fields using the actual field names:

(IsNull([Approval])+IsNull([Action]))<>-1

but Access gave me the following error message when I tried to leave
Table Design View:

"Invalid SQL syntax - cannot use multiple columns in a column-level
CHECK constraint.

Any idea how to overcome this problem?

You can't put this in the validation rule for a *field*. It has to be in the
validation rule for the *table*.

In table design view bring up the property sheet and you will see the table
validation rule property there.

 

[Paul Ponzelli]

Ah - now it's working.

Thanks for your help with this, Rick.

 

[Tim Ferguson]

Use a Table-Level validation rule like...

(IsNull([Field1])+IsNull([Field2]))<>-1

Shouldn't that be

IsNull([Action]) = IsNull([Approval])



Mind you, in an ideal DB it should be possible to define a single field
with a domain restricted to {"Approval Denied" OR DATETIME) since that is
actually what you are trying to model.

B Wishes


Tim F

 

[Paul Ponzelli]

in an ideal DB it should be possible to define a single field

with a domain restricted to {"Approval Denied" OR DATETIME) since that is
actually what you are trying to model.

Thanks for chiming in, Tim. However, we actually want to know the date it
was denied as well as the date it was approved. We not only need to know
whether an application was approved and when, but we also need to know how
long it takes us to process the application, regardless of whether they were
approved or denied. The reason for this is that we use both time intervals
in our performance metrics. Since we track two discrete bits of
information, we need two fields.

Would you agree?

 

[Douglas J. Steele]

At the risk of starting a religious war <g>, why do you need the Yes/No
field? The presence of something in the Explanation field tells you Yes or
No. If you want to be truly pedantic, you could argue that you need a
zero-length string in the Explanation to mean No, since Null could mean the
Yes/No field was Null or that it was No.

Yes, you'd want both the Yes/No field and the Explanation on the form, but
that doesn't mean it's necessary in the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You can put code into the form's BeforeUpdate event to check the two
fields and cancel the update if they're not okay. However, I have to
question your design. Having fields whose values are dependent on one
another doesn't sound like a properly normalized table.

I disagree. In this case the values are not dependent on each other, only
the requirement to have a value is.

It is quite common for instance with Question/Answer applications to
require that an [Explanation] or [Details] field be filled out when
certain responses are filled in, but not when others are. A good example
is a medical database...

Are you currently taking any medication? ( ) Yes ( ) No

If "Yes" please list ____________

 

[Douglas J. Steele]

See my answer to Rick.

It's not a major thing, though: not on the same level as repeating fields,
for instance.

 

[Rick Brandt]

At the risk of starting a religious war <g>, why do you need the Yes/No field?
The presence of something in the Explanation field tells you Yes or No. If you
want to be truly pedantic, you could argue that you need a zero-length string
in the Explanation to mean No, since Null could mean the Yes/No field was Null
or that it was No.

Yes, you'd want both the Yes/No field and the Explanation on the form, but
that doesn't mean it's necessary in the table.

Okay, that was a weak example. How about additional fields that would be
required to be filled out if you indicated "Female" for the field indicating
sex? I have also had databases where an option group was used and one of the
choices was "Other". When that choice was taken an [Explanation] field was then
required to be filled out which was otherwise not required.

 

[Tim Ferguson]

Would you agree?

OK: must have misread the original description.

All the best


Tim F