Restrict data in one field based on content of another

[Pam]

In my 'Repairs Tracking' database entry form I have a drop down field called
'status' which establishes whether the repair is 'current', 'on hold',
'pending' or 'completed'. Another field on the form is called 'Invoice No'
with the default set to "Not Invoiced"
I want to restrict entries on the 'status' field where if someone selects
'completed' it requires that the 'Invoice No' field must NOT be "Not
Invoiced". In other words if a repair has not been invoiced, it cannot be
set to "completed" status.

 

[Allen Browne]

1. Open your table in design view.

2. Open the Properties box (view menu.)

3. In the Validation Rule in the Properties box (not the one in the lower
pane of table design, which only relates to one field), enter this
expression:
([Status] <> "completed") OR ([Status] Is Null) OR ([Invoice No] Is Not
Null)

4. Save.

Access checks the validation rule after you've filled in all the fields, but
before it saves the record. At this point, there are 3 says the rule can be
satisfied:
a) If the Status is anything other than completed, it's happy.
b) If the Status is blank, it's happy.
c) If there is an invoice number, it's happy.
What it will not save, then, is a record where the Status is "completed" and
the invoice number is blank.

For more details about validation rules, see:
http://allenbrowne.com/ValidationRule.html

 

[Pam]

Thank you so much, works great!

1. Open your table in design view.

2. Open the Properties box (view menu.)

3. In the Validation Rule in the Properties box (not the one in the lower
pane of table design, which only relates to one field), enter this
expression:
([Status] <> "completed") OR ([Status] Is Null) OR ([Invoice No] Is Not
Null)

4. Save.

Access checks the validation rule after you've filled in all the fields, but
before it saves the record. At this point, there are 3 says the rule can be
satisfied:
a) If the Status is anything other than completed, it's happy.
b) If the Status is blank, it's happy.
c) If there is an invoice number, it's happy.
What it will not save, then, is a record where the Status is "completed" and
the invoice number is blank.

For more details about validation rules, see:
http://allenbrowne.com/ValidationRule.html

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

Reply to group, rather than allenbrowne at mvps dot org.

In my 'Repairs Tracking' database entry form I have a drop down field
called
'status' which establishes whether the repair is 'current', 'on hold',
'pending' or 'completed'. Another field on the form is called 'Invoice
No'
with the default set to "Not Invoiced"
I want to restrict entries on the 'status' field where if someone selects
'completed' it requires that the 'Invoice No' field must NOT be "Not
Invoiced". In other words if a repair has not been invoiced, it cannot be
set to "completed" status.