mutually exclusive fields

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have a form, Assets Form, based on a query, Assets
Query, based on a table, Assets Table. The form contains
a field entitled CurrentValue and another entitled
LiquidationDate. I want to ensure that if there is a
CurrentValue entered that user cannot enter a
LiquidationDate until the CurrentValue is deleted, and
vice versa--and that if they violate the rule an
appropriate message box will appear. I suspect that
accomplishing this has something to do with validation
rules, but I'm not sure. Please advise how to best
accomplish this, hopefully without resorting to VB,
though I can take a swing at that if necessary.

Thank you advance.

Jim
 
Open your table in design view.
Open the Properties box (View menu).

Beside the Validation Rule in the Properties box, enter one of these
expressions:
([CurrentValue] Is Null) Xor ([LiquidationDate] Is Null)

Not (([CurrentValue] Is Not Null) And ([LiquidationDate] Is Not Null))

Use the first one if you want to insist that either one or the other is
entered.
Use the second one if you only want to insist that they cannot both the
entered (i.e. it's okay if they are both blank.)

The validation rule for the table is different from the validation rule for
the field (in the lower pane of table design). To compare two fields, you
need to use the validation rule of the table.
 
Thank you. I appreciate the background and the solution.
It is just what I needed.

How about if there another field, LiquidationAmount, that
I would also like to compare with CurrentValue. Is there
an appropriate expression to compare two fields with one
via the Validation Rule? Specifically, I would want to
forbid entering a LiquidationDate or LiquidationValue
when there is an entry for CurrentValue and vice versa.
From your note I'm guessing something like the following
might work (though I'll bet additonal parentheses are
needed somewhere):

Not (([CurrentValue] Is Not Null) And ([LiquidationDate]
Is Not Null)OR [LiquidationAmount]Is Not Null))

Thanks again. I appreciate your being so generous with
your time and knowledge.

Jim
-----Original Message-----
Open your table in design view.
Open the Properties box (View menu).

Beside the Validation Rule in the Properties box, enter one of these
expressions:
([CurrentValue] Is Null) Xor ([LiquidationDate] Is Null)

Not (([CurrentValue] Is Not Null) And
Click to expand...
([LiquidationDate] Is Not Null))
 
I messed around with the language previously provided and
came up with a validation rule expression that seems to
work as follows:

Not ((([CurrentValue/Unit] Is Not Null) And
([CurrentValue/Unit]<>0))
And (([LiquidDate] Is Not Null) OR (([LiquidAmount] Is
Not Null)AND([LiquidAmount]<>0))))

Thanks again for the help
-----Original Message-----
Thank you. I appreciate the background and the solution.
It is just what I needed.

How about if there another field, LiquidationAmount, that
I would also like to compare with CurrentValue. Is there
an appropriate expression to compare two fields with one
via the Validation Rule? Specifically, I would want to
forbid entering a LiquidationDate or LiquidationValue
when there is an entry for CurrentValue and vice versa.
From your note I'm guessing something like the following
might work (though I'll bet additonal parentheses are
needed somewhere):

Not (([CurrentValue] Is Not Null) And ([LiquidationDate]
Is Not Null)OR [LiquidationAmount]Is Not Null))

Thanks again. I appreciate your being so generous with
your time and knowledge.

Jim
-----Original Message-----
Open your table in design view.
Open the Properties box (View menu).

Beside the Validation Rule in the Properties box, enter one of these
expressions:
([CurrentValue] Is Null) Xor ([LiquidationDate] Is Null)

Not (([CurrentValue] Is Not Null) And
Click to expand...
([LiquidationDate] Is Not Null))
Use the first one if you want to insist that either one or the other is
entered.
Use the second one if you only want to insist that they cannot both the
entered (i.e. it's okay if they are both blank.)

The validation rule for the table is different from the validation rule for
the field (in the lower pane of table design). To compare two fields, you
need to use the validation rule of the table.

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

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




.
Click to expand...
.
Click to expand...
 
Back
Top