Field1 or Field2 must be null

  • Thread starter Thread starter Dan Neely
  • Start date Start date
D

Dan Neely

Is it possible to set a constraint on a table across two fields such
that one of the pair must have a value and the other be null?
 
Dan said:
Is it possible to set a constraint on a table across two fields such
that one of the pair must have a value and the other be null?
Click to expand...


You can use the table's Validation Rule:

[f1] Is Not Null Or [f2] Is Not Null
 
Is it possible to set a constraint on a table across two fields such
that one of the pair must have a value and the other be null?
Click to expand...

well, such a table might be arguably improperly normalized: a field should
depend only on the primary key, not the value of another field!

But you can use "Table Validation" - not field validation; view the table's
properties and set its Validation Rule to

(FieldA Is Null AND FieldB Is Not Null) OR (FieldA Is Not Null And FieldB Is
Null)

and the validation text to some meaningful message like "You must fill in
either FieldA or FieldB but not both".

John W. Vinson [MVP]
 
Marsh,

The OP is looking for one to be Null and the Other to have value. I believe
that as coded, you will get True if both have value. I suggest:

([f1] Is Not Null And [f2] Is Null) Or ([f1] Is Null And [f2] Is Not Null)

--
Dave Hargis, Microsoft Access MVP


Marshall Barton said:
Dan said:
Is it possible to set a constraint on a table across two fields such
that one of the pair must have a value and the other be null?
Click to expand...


You can use the table's Validation Rule:

[f1] Is Not Null Or [f2] Is Not Null
Click to expand...
 
well, such a table might be arguably improperly normalized: a field should
depend only on the primary key, not the value of another field!
Click to expand...

*nod* MY DB background is rather minimal I'm not entirely sure if
what I want to do is a good idea.

Essentially I have two tables with different types of objects Foo and
Bar. The contents of these wouldn't make sense to be merged. Both
Foo and Bar objects however are processed by the same group of users
and follow an almost identical workflow. Tasking for the users is
currently stored in seperate link tables for each. both store an ID
for the user, and an ID for the task, with the only difference being
that one stores a FooID and the other a BarID. What I was considering
doing was to create a single assigned work table for both Foo and Bar
objects. This would require having a FooID or a BarID (but not both)
in every row.
 
Klatuu said:
Marsh,

The OP is looking for one to be Null and the Other to have value. I believe
that as coded, you will get True if both have value. I suggest:

([f1] Is Not Null And [f2] Is Null) Or ([f1] Is Null And [f2] Is Not Null)
Click to expand...


Ah yes, I need to read more carefully,
Thanks for the correction Dave.
 
*nod* MY DB background is rather minimal I'm not entirely sure if
what I want to do is a good idea.

Essentially I have two tables with different types of objects Foo and
Bar. The contents of these wouldn't make sense to be merged. Both
Foo and Bar objects however are processed by the same group of users
and follow an almost identical workflow. Tasking for the users is
currently stored in seperate link tables for each. both store an ID
for the user, and an ID for the task, with the only difference being
that one stores a FooID and the other a BarID. What I was considering
doing was to create a single assigned work table for both Foo and Bar
objects. This would require having a FooID or a BarID (but not both)
in every row.
Click to expand...

If you have full control over the Foo and Bar tables, then you might want to
consider a slightly different approach: "Subclassing". You could have a Work
table with a primary key FooBarID, and a field distinguishing whether the
record refers to a Foo or a Bar; the Foo and Bar tables would both have
FooBarID as their Primary Key (some values would exist in the Foo table,
others in the Bar table, presumably none in both). You'ld have one to one
relationships from Work to Foo, and from Work to Bar; any fields that pertain
to the workflow task (e.g. the user ID) would be in the Work table.

John W. Vinson [MVP]
 
Back
Top