Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Expression For Two Conditional Tests

Status
Not open for further replies.

iamchemist

Programmer
Mar 2, 2009
73
US
I have a need to write an expression in a Query, which performs two conditional tests at once. That is, something like "If X is true and Y is true, then print Name", or "If X=N and Y=M, then print Name". It would be ideal if the Immediate IF Function (IIf) would allow two tests within the statement, but as far as I can tell it will only allow one.

I am aware that what I want to do can be done with VBA Code (although I'm not really sure how to do it), but I'd rather not have to go that route.

Any ideas?

Thanks,

Ron
 
iif(expression,Truepart,falsePart)
can be nested
iif(expression,TruePart,iif(expression2,TruePart,FalsePart))
 
Or simply
Code:
iif((X="N") AND (Y="M"), trueresult, falseresult)

Iif will take a complex statement as an argument - you may or may not have to include it in extra parenthesis.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard
 
MajP,

I have tried what I believe you suggested, with an expression like:

Expr3: IIf([Expr2]="1",[Name],IIf([Expr1]="T-",[Name],""))

Where [Expr2] and [Expr1] are calculated elsewhere in the same query.

But the result looks more like an "OR" result instead of an "AND". What happens is that the "true part", that is listing [Name], seems to occur if either [Expr2] or [Expr1] is true.

Am I doing something wrong?

Thanks,

Ron
 
Expr3: IIf([Expr2]="1" AND [Expr1]="T-",[Name],"")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Expr3: IIf([Expr2]="1",[Name],IIf([Expr1]="T-",[Name],""))
But the result looks more like an "OR" result instead of an "AND".
That's because this really IS like an OR.
If you wrote this same expression in VBA it would be:
Code:
If Expr2 = "1" Then
   [Name]
Else
    If Expr1 = "T-" Then
        [Name]
    Else
        Nothing
    End If
End If
The test for Expr1 will only happen if the Expr2 test is FALSE. You need BOTH expressions evaluated, such as the method provided by PHV.


Randy
 
PHV,

It works!! Thank you so much!

I had tried that approach, but running the query always resulted in a question box asking me to enter a value for Expr2. So, I gave up on the approach. It turns out that you get that question, if the query "Total" line is set to "Group By" instead of to "Expression".

Thanks again for the help,

Ron
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top