Validation of one field based on another

[Serena]

Hi
Using Access 97

1) I have a table with (amongst others) two fields, Status
and Action. How can I set the validation on the Action
field so that it is not allowed to be Null if the Status
field is "Actioned" ?

2) I have a reference table which has From and To date
fields. Is there any easy way (i.e. no VB) of checking
that none of the records overlap i.e. no From or To date
falls between the range of the From and To dates on any
other record ? (I have managed to set the validation to
ensure the To date > than the From date on a given
record !)

TIA
Serena

 

[Allen Browne]

A1: Validation:
Open the table in design view.
Open the Properties box.
Enter this for the Validation Rule in the Properties box. (Note: don't use
the Validation rule in the lower pane of table design: that applies to a
field, not the table.):

([Action] Is Null) Or ([Status]<>"Actioned") Or ([Status] Is Null)

Explanation:
The rule is satisfied in any of these 3 cases:
- Action is not null (regardless of what Status is),
- Status is not "Actioned" (regardless of what Action is),
- Status is Null (you may not want this one).
It follows that the Rule is not satisfied if Status is "Actioned" and Action
is not null.


A2: Overlap:
See:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html
The article explains how to use two copies of the same table without a join
(a cartesian product) to identify overlaps.

 

[Serena]

Thanks for that Allen. I found it worked when I replaced
the 'or' with an 'and'. Haven't had a chance to look at
the clashing appts yet but the theory looks good !

-----Original Message-----
A1: Validation:
Open the table in design view.
Open the Properties box.
Enter this for the Validation Rule in the Properties box. (Note: don't use
the Validation rule in the lower pane of table design: that applies to a
field, not the table.):

([Action] Is Null) Or ([Status]<>"Actioned") Or ([Status] Is Null)

Explanation:
The rule is satisfied in any of these 3 cases:
- Action is not null (regardless of what Status is),
- Status is not "Actioned" (regardless of what Action is),
- Status is Null (you may not want this one).
It follows that the Rule is not satisfied if Status is "Actioned" and Action
is not null.


A2: Overlap:
See:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html
The article explains how to use two copies of the same table without a join
(a cartesian product) to identify overlaps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Hi
Using Access 97

1) I have a table with (amongst others) two fields, Status
and Action. How can I set the validation on the Action
field so that it is not allowed to be Null if the Status
field is "Actioned" ?

2) I have a reference table which has From and To date
fields. Is there any easy way (i.e. no VB) of checking
that none of the records overlap i.e. no From or To date
falls between the range of the From and To dates on any
other record ? (I have managed to set the validation to
ensure the To date > than the From date on a given
record !)

TIA
Serena

.