Yes/No Fields

On Mon, 27 Oct 2008 08:52:10 -0700, Task Database Nightmare
wrote:

Obviously I am doing something wrong. It works when I just use one parameter
but when I add the others, it doesn't work anymore. Below is what I have
noted in the Table Properties. I tried it both with OR and AND and neither
works. Help please - thanks!

It's pretty tricky! You need a mix of OR and AND, and it needs to be the
correct mix.

The validation rule is a Boolean algebra statement using the OR operator (A OR
B = True if A is true, or B is true, or both are true) and the AND operator (A
AND B is true if and only if both A and B are true); if the entire expression
is TRUE the record is valid. You need all five pairs of conditions to be TRUE
(whether true by virtue of the checkbox *or* by virtue of the non-null field)
so you need some parenthesis nesting and some ANDs:

([Print]=False Or [Target Print Date] Is Not Null)
AND
([Supply]=False Or [Target Supply Date] Is Not Null)
AND
([email]=False Or [Email Target Date] Is Not Null)
AND
([Mail]=False Or [Mail Target Date] Is Not Null)
AND
([Posting]=False Or [Posting Target Date] Is Not Null)



--

John W. Vinson [MVP]

Ok - it worked in the Table. If I enter a task in the table it works - but
not in the Form?

"John W. Vinson" wrote:

On Mon, 27 Oct 2008 08:52:10 -0700, Task Database Nightmare
wrote:

Obviously I am doing something wrong. It works when I just use one parameter
but when I add the others, it doesn't work anymore. Below is what I have
noted in the Table Properties. I tried it both with OR and AND and neither
works. Help please - thanks!

It's pretty tricky! You need a mix of OR and AND, and it needs to be the
correct mix.

The validation rule is a Boolean algebra statement using the OR operator (A OR
B = True if A is true, or B is true, or both are true) and the AND operator (A
AND B is true if and only if both A and B are true); if the entire expression
is TRUE the record is valid. You need all five pairs of conditions to be TRUE
(whether true by virtue of the checkbox *or* by virtue of the non-null field)
so you need some parenthesis nesting and some ANDs:

([Print]=False Or [Target Print Date] Is Not Null)
AND
([Supply]=False Or [Target Supply Date] Is Not Null)
AND
([email]=False Or [Email Target Date] Is Not Null)
AND
([Mail]=False Or [Mail Target Date] Is Not Null)
AND
([Posting]=False Or [Posting Target Date] Is Not Null)



--

John W. Vinson [MVP]

On Mon, 27 Oct 2008 10:04:01 -0700, Task Database Nightmare
wrote:

Ok - it worked in the Table. If I enter a task in the table it works - but
not in the Form?

What "doesn't work"? Surely the form is bound to the table? What happens when
you enter an invalid record via the form?

You can also use VBA code in the Form's BeforeUpdate event to check the
validity of the data:

([Print]=False) Or ([Target Print Date] Is Not Null) Or ([Supply]=False) Or
([Target Supply Date] Is Not Null) Or ([email]=False) Or ([Email Target Date]
Is Not Null) Or ([Mail]=False) Or ([Mail Target Date] Is Not Null) Or
([Posting]=False) Or ([Posting Target Date] Is Not Null)

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!Print Then
If IsNull(Me![Target Print Date]) Then
MsgBox "Please fill in Target Print Date"
Me![Target Print Date].SetFocus
Cancel = True
End If
End If

similar code for the rest

One question: might it make sense to have all of the target dates simply have
a Default Value of Date(), or some other suitable default, so the user can
just accept the default rather than filling them all in?

--

John W. Vinson [MVP]