Validation Rule

[David]

Hey I have a table that has two fields that have a relationship I would like
to enforce.

The fields are delivered and cancelled. They are both Yes/No Fields.

I would like to make a rule for both of them that states they both can't be
equal to yes to avoid user errors. Anyone know how I could do this? Thanks

Dave

 

[Brendan Reynolds]

In a table (not field) validation rule ...

delivered = False Or cancelled = False

To create a table validation rule, open the table in design view and select
Properties from the View menu.

 

[David]

I don't know why but its not working correctly. I am getting an error
"Error 'Error evaluating CHECK constraint. ' in the validation rule." Any
ideas?

 

[Brendan Reynolds]

Access has 'helpfully' decided that when you entered the field names
cancelled and delivered, you must have really intended to enter the literal
text values 'cancelled' and 'delivered', and so it has 'thoughtfully'
inserted for you the quotes which you 'forgot' to type! :-/

Try ...

[cancelled]=False Or [delivered]=False

The square brackets will discourage Access from being so 'helpful'.

 

[David]

It's working. I went into the expression builder and recreated it. The
code it outputted was [Delivered]=No Or [Cancelled]=No.

Thanks for the help Brendan! Great advice!

 

[Jamie Collins]

Hey I have a table that has two fields that have a relationship I would like
to enforce.

The fields are delivered and cancelled. They are both Yes/No Fields.

I would like to make a rule for both of them that states they both can't be
equal to yes to avoid user errors.

Because constraints on bit mask fields have maintenance issues **, you
may find it easier to have a single state column: easy to define, easy
to amend, easy to debug, easy for the next guy to understand, etc. It
would certainly make you data more dolphin friendly... erm, I mean,
human readable.

Also, you may find you have a business need for transition states (e.g.
to ensure an item doesn't go direct from 'basket' to 'delivered'
without going via 'checkout' or whatever) which would be very difficult
to achieve with multiple interdependent flags. See:

Transition Constraints
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko35

** There are other reasons e.g. the incompatibility of Boolean data
with SQL's three value logic, portability, logic with 'negative' names
e.g. NOT NeverInactive = FALSE etc.

Jamie.

--

 

[Jamie Collins]

Hey I have a table that has two fields that have a relationship I would like
to enforce.

The fields are delivered and cancelled. They are both Yes/No Fields.

I would like to make a rule for both of them that states they both can't be
equal to yes to avoid user errors.

I understand you may have chosen this design because it is conducive
for bound controls for the table to resemble the input form. In which
case, you could use DRI (foreign key, Relationship enforcing data
integrity, whatever it's called in the UI): put the valid states in a
table. I think this might be easier to maintain.

In SQL DDL code it would look like this:

CREATE TABLE PackageStates (
delivered YESNO NOT NULL,
cancelled YESNO NOT NULL,
PRIMARY KEY (delivered, cancelled)
)
;
INSERT INTO PackageStates (delivered, cancelled)
VALUES (TRUE, FALSE)
;
INSERT INTO PackageStates (delivered, cancelled)
VALUES (FALSE, TRUE)
;
INSERT INTO PackageStates (delivered, cancelled)
VALUES (FALSE, FALSE)
;
CREATE TABLE Packages (
order_ID INTEGER NOT NULL PRIMARY KEY,
delivered YESNO NOT NULL,
cancelled YESNO NOT NULL,
FOREIGN KEY (delivered, cancelled)
REFERENCES PackageStates (delivered, cancelled)
)
;

This would be OK:

INSERT INTO Packages (order_ID, delivered, cancelled)
VALUES (1, FALSE, FALSE)
;

This would fail because delivered=TRUE, cancelled=TRUE is an illegal
state:

INSERT INTO Packages (order_ID, delivered, cancelled)
VALUES (2, TRUE, TRUE)
;

Jamie.

--