IIF Function. Check boxes. Help! 1

[indiana45s]

I am getting desperate for a solution.
I have a table set up with check boxes for two fields
Investigation and Investigation Completed.
I would like to make a Query in which the table instance passes the Query ONLY if BOTH boxes are checked YES, not either one.
I am very new to queries and the most advanced I have gotten is putting "Yes" in the criteria of a query field so that the event is passed through the query only if the box is checked.
However, in this instance TWO boxes must be checked to pass the query test, not one.

I have tried to write the following expression into the criteria:

If([clients]![Investigation]=Yes AND [clients]![Investigationcomplete]=Yes, yes, no].

However, this does not work.
Help!

 

[Blorf]

Hi. Try in your query

Test[clients]![Investigation] + [clients]![Investigationcomplete])

With -2 as the condition.

ChaZ



There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[PHV]

Have you tried to put "Yes" in BOTH cells ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Blorf]

Sorry, the yes in both is obvious.

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[indiana45s]

I believe I failed to explain myself.
The test must pass if both are checked.
It must also pass if NEITHER are checked.
So putting YES in both boxes will not work. Because it will not pass where both are NO.

 

[earthandfire]

Test:IIf(([Investigation] And [InvestigationComplete]) Or (Not([Investigation]) And Not([InvestigationComplete])),True,False)

 

[Blorf]

Back to PHV, Yes Yes, under that, No NO, this will exclude both yes no, and no yes.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[earthandfire]

Blorf, I didn't notice PHV's answer. I saw yours and the detailed explanation from indiana45s and simply edited yours.

As usual PHV has hit the nail on the head with the neatest and simplest solution (as modified by you of course )

 

[indiana45s]

Many thanks to all of you
YES/YES
or
NO / NO worked very well and was extremely elegant. I feel as though I was psyching myself out on this one trying to build crazy elaborate tests.

Thanks again!

 

[danvlas]

The test must pass if both are checked.
It must also pass if NEITHER are checked."


Test: [Investigation] Xor [InvestigationComplete]
Condition: 0


XOR: Exclusive OR (only one condition should be true to return TRUE, in all other cases the result is False)

And it will return both cases in one step.

It does NOT apply for NULL values.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Blorf]

Bitwise logic in a query. Who would have thunk it? F1 doesn't mention it. Is there a regular or and an And available?

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[raskew]

Daniel -

An elegant solution using a technique that doesn't get a whole lot of air-time.

It's a keeper. Thanks.

Bob