conditional validation rules

[buscher75]

I have set a validation rules for a table and also for fields. Now I need to
set another table validation rule to say:
If field1 = “completeâ€, then field2 must be greater than zero (it is a
numeric field).
If field1 = “incompleteâ€, then field 2 can be blank or zero.

I have set a validation rule for field1 to either “complete†or “incompleteâ€
and made it a required field. I want to make field2 a required field also.

I am unsure of how to enter this into the table’s validation rule, or even
the field2 validation rule. Do I just start another line and start typing a
second rule in VB mode for the table? Or do I have to identify each
validation rule? I hope this makes sense.

 

[Allen Browne]

In table design view, open the Properties box.

Beside the Validation Rule in the Properites box, enter:
([Field2] > 0) OR ([Field1] <> "complete")

If Field2 is not greater than zero, the rule is unsatisifed if Field1 is
"complete".

(Note that the above doesn't handle cases where one of the fields is null.)

More examples:
http://allenbrowne.com/ValidationRule.html

 

[buscher75]

What you have said makes sence.....

I need to key in this validation rule for 3 different scenarios. A hardware
kit, control box kit and an evaporator kit. So, just to clarify, when I open
the tables property box and hit the ... Button for the table's validation
rule, my code should look like this:

([HWweight] > 0) OR ([Hardware] <> "complete")
([CBweight] > 0) OR ([Control] <> "complete")
([EVweight] > 0) OR ([Evaporator] <> "complete")

Could I also apply the same concepts if the boxes are incomplete, then? And
add three more conditons? The three fields, hardware, control, evaporator,
are required and will only accept "complete" or "incomplete" under their
validation rules.

([HWweight] >= 0) OR ([Hardware] <> "incomplete")
([CBweight] >= 0) OR ([Control] <> "incomplete")
([EVweight] >= 0) OR ([Evaporator] <> "incomplete")

Thanks for your time and knowledge!

In table design view, open the Properties box.

Beside the Validation Rule in the Properites box, enter:
([Field2] > 0) OR ([Field1] <> "complete")

If Field2 is not greater than zero, the rule is unsatisifed if Field1 is
"complete".

(Note that the above doesn't handle cases where one of the fields is null.)

More examples:
http://allenbrowne.com/ValidationRule.html

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

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

I have set a validation rules for a table and also for fields. Now I need
to
set another table validation rule to say:
If field1 = “completeâ€, then field2 must be greater than zero (it is a
numeric field).
If field1 = “incompleteâ€, then field 2 can be blank or zero.

I have set a validation rule for field1 to either “complete†or
“incompleteâ€
and made it a required field. I want to make field2 a required field
also.

I am unsure of how to enter this into the table’s validation rule, or even
the field2 validation rule. Do I just start another line and start typing
a
second rule in VB mode for the table? Or do I have to identify each
validation rule? I hope this makes sense.

 

[buscher75]

I was finally able to get into the be of my database. When I added the
addtional validation rules, I got an error. "The expression you entered
has an invalid .(dot) or ! operator or invalid parentheses."

Is this because I already have one validation rule fo the table? Maybe I
should put this in the form's before update property; to check values,
althought I do not know exactly how to write the code.

What you have said makes sence.....

I need to key in this validation rule for 3 different scenarios. A hardware
kit, control box kit and an evaporator kit. So, just to clarify, when I open
the tables property box and hit the ... Button for the table's validation
rule, my code should look like this:

([HWweight] > 0) OR ([Hardware] <> "complete")
([CBweight] > 0) OR ([Control] <> "complete")
([EVweight] > 0) OR ([Evaporator] <> "complete")

Could I also apply the same concepts if the boxes are incomplete, then? And
add three more conditons? The three fields, hardware, control, evaporator,
are required and will only accept "complete" or "incomplete" under their
validation rules.

([HWweight] >= 0) OR ([Hardware] <> "incomplete")
([CBweight] >= 0) OR ([Control] <> "incomplete")
([EVweight] >= 0) OR ([Evaporator] <> "incomplete")

Thanks for your time and knowledge!

In table design view, open the Properties box.

Beside the Validation Rule in the Properites box, enter:
([Field2] > 0) OR ([Field1] <> "complete")

If Field2 is not greater than zero, the rule is unsatisifed if Field1 is
"complete".

(Note that the above doesn't handle cases where one of the fields is null.)

More examples:
http://allenbrowne.com/ValidationRule.html

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

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

I have set a validation rules for a table and also for fields. Now I need
to
set another table validation rule to say:
If field1 = “completeâ€, then field2 must be greater than zero (it is a
numeric field).
If field1 = “incompleteâ€, then field 2 can be blank or zero.

I have set a validation rule for field1 to either “complete†or
“incompleteâ€
and made it a required field. I want to make field2 a required field
also.

I am unsure of how to enter this into the table’s validation rule, or even
the field2 validation rule. Do I just start another line and start typing
a
second rule in VB mode for the table? Or do I have to identify each
validation rule? I hope this makes sense.

 

[John W. Vinson/MVP]

I was finally able to get into the be of my database. When I added the
addtional validation rules, I got an error. "The expression you entered
has an invalid .(dot) or ! operator or invalid parentheses."

What exactly did you enter? Copy and paste the entire validation rule.
A Table can have only ONE validation rule, not several; you may need
to get into some pretty murky Boolean logic expressions to combine AND
and OR logic appropriately.

It's often easier and more user-friendly to put this kind of
validation in VBA code in a Form's BeforeUpdate event. It's not as
secure - someone could bypass the form - so you may need the complex
table validation rule as well.

 

[buscher75]

I appeciate you insight on this topic. I did not know you could only have
one validation rule for a table, which I already have. I added code to the
before update event on my form and it works good enough. I appreciate you
time and thank you.