basing a field validation rule on another field in same table...

[Brad Pears]

Is there an easy way to set a validation rule for a table field that is
based on the value that happens to exist in another field in that same
table?

What I would like to do is that if "field1" has a certain value in it, say
"4", then I want the validation rule for "field2" to be >0 - meaning if
there is a 4 in field1, then something HAS to be a value in field2. However,
if there is anything other than a "4" in field1, then it does not matter
what is in field2... Get?

Any way to do this at the db level? I know I can enforce this through code
but it would be better to do it at the db level if possible...

Thanks,

Brad

 

[Rick Brandt]

Is there an easy way to set a validation rule for a table field that
is based on the value that happens to exist in another field in that
same table?

What I would like to do is that if "field1" has a certain value in
it, say "4", then I want the validation rule for "field2" to be >0 -
meaning if there is a 4 in field1, then something HAS to be a value
in field2. However, if there is anything other than a "4" in field1,
then it does not matter what is in field2... Get?

Any way to do this at the db level? I know I can enforce this through
code but it would be better to do it at the db level if possible...

Thanks,

Brad

Not a *Field* Validation Rule, but a Table Validation Rule of...

IIf([Field2] Is Null, 3, [Field2]) > IIf([Field1] = "4", 2, 0)

....might do it. I tested it only briefly so it might need additional
tweaking.

You have to view the property sheet of the table while in design view to
gain access to the table level Validation Rule.

 

[Brad Pears]

Thanks.. that was really close - I have it working now!! Now I know where
to put table validation stuff like that.. always wondered!!!

Brad

Is there an easy way to set a validation rule for a table field that
is based on the value that happens to exist in another field in that
same table?

What I would like to do is that if "field1" has a certain value in
it, say "4", then I want the validation rule for "field2" to be >0 -
meaning if there is a 4 in field1, then something HAS to be a value
in field2. However, if there is anything other than a "4" in field1,
then it does not matter what is in field2... Get?

Any way to do this at the db level? I know I can enforce this through
code but it would be better to do it at the db level if possible...

Thanks,

Brad

Not a *Field* Validation Rule, but a Table Validation Rule of...

IIf([Field2] Is Null, 3, [Field2]) > IIf([Field1] = "4", 2, 0)

...might do it. I tested it only briefly so it might need additional
tweaking.

You have to view the property sheet of the table while in design view to
gain access to the table level Validation Rule.