IIf - why is it evaluating both responses?

Stephanie Hudson
Stephanie Hudson used Ask the Experts™
on
Setup:
I am generating a price list report.
I have a form where the user can select the "code" that corresponds with the correct retail or wholesale values to populate the price list.
If the form is NOT loaded, I want it to just give me the default values (which is the wholesale price).

I was hoping to have the report recordsource be one query where I use ELookup to find the correct markups based on the form selection.

So.. In a query, I'm using an IIf statement to determine if the form is loaded.

What I want is this:
If the form is loaded, then use the information from that form to calculate the selling prices, else just give me the value without the calculation (i.e. the base wholesale price).

What's happening is that when the form is not loaded (the eval equals false), the query still asks me for the parameter on the form anyway (as though it's processing the true response).  If I just hit enter (don't give it a response), the value shown in the resulting query is correct (that is, just the value without any calculation).  But why is it asking me for the parameter at all?

What I'm going to end up doing (at least until you experts can tell me what's going on) is have two queries and just change the recordsource of the report based on whether the form is open or not in the report's OnOpen property.  Not as cool as I 'd like.  :)

Anyway, here is one of my price calculations:
CHMQPrice: IIf(fIsLoaded("frmChoosePriceList"),IIf([CHMQ],(RoundToLarger(RoundToLarger([CHMQ]*ELookup("WholesaleMultiplier","tblPriceListMarkups","(((MarkupCode)= '" & [Forms]![frmChoosePriceList].[Choose] & "'))"),0)*ELookup("RetailMultiplier","tblPriceListMarkups","(((MarkupCode)= '" & [Forms]![frmChoosePriceList].[Choose] & "'))"),0)),Null),[CHMQ])

..... When form not loaded, asks for Forms!frmChoosePriceList.Choose, but then returns the value of [CHMQ] correctly in the query result.

Striped of calculations to get all those extra parentheses out of there:
CheckingIffs: IIf(fIsLoaded("frmChoosePriceList"),IIf([CHMQ],[DummyParameter],"LoadedAndNoPrice"),"NotLoaded")

..... When form not loaded, asks for DummyParameter, but then returns "NotLoaded" correctly in the query result.


Striped to be as simple as possible:
CheckingIffSimple: IIf(1=2,[WhyOhWhy],False)

..... When form not loaded, asks for WhyOhWhy, but then returns 0 correctly in the query result.


Oh, and by the way, it still does it with the reverse:
CheckingIffs: IIf(fIsLoaded("frmChoosePriceList")=0,"NotLoaded",[DummyParameter])

..... When form not loaded, still asks for DummyParameter, but then returns "NotLoaded" correctly in the query result.


Why is it still looking for those parameter values?  Isn't that the whole purpose of an IIf statement is to bypass calcs you don't want?

Obviously, I don't want it to ask for the query parameter (and confuse my users) if they somehow open the report without using the form.  Or, for when I'm making iterations on the report design - I don't want to have to use the form each time I want to print preview my report.

I know there's a workaround, but this just doesn't make sense to me.  If someone can tell me this is "as designed" - okay.  But maybe I'm being dumb and someone can fix this for me.  :)


Note:  fIsLoaded, ELookup, and RoundToLarger are custom functions that can all be easily found using Google.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
>>Isn't that the whole purpose of an IIf statement is to bypass calcs you don't want?Alas, no.  IIf will still evaluate the "if false" part even if the condition is true.  That behavior is by design.  I wish it weren't so, but there it is.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
IIF ..
or even in VBA/VB script, if <expr1> or <expr2>

Both sides will be evaluated because it is not using "boolean shortcut logic", which stops as soon as an answer is found.  It is by nature of design of the VB language as implemented by Microsoft.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
From the Help File:

"IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True."

mx
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
>>Isn't that the whole purpose of an IIf statement is to bypass calcs you don't want?

That is the misunderstanding.  access/VB is not js/java/c/c#/delphi...
No it is not.
It is just there for people to write shorthand (cond, expr-if, expr-else), while fully evaluating all 3 parts.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007
Commented:
You might consider rewriting this using the Switch () function, since:

"The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, ******>>> and the value associated with the first expression to evaluate to True is returned.**** If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on."

Syntax

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])


So, Switch () alleviates this issue.

mx
Top Expert 2010
Commented:
MX,I ran these in the VB Editor Immediate Window and not in a query, but the results suggest that Switch falls victim to this as well.?switch(true,"foo",(6/0)=0,"hee")generates a div/0 error, even though the first condition is true.?switch(false,1/0,true,0/1)also generates a div/0 error, even though the first condition is false, and so you would not expect that expression to be evaluated.:)Patrick
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Well ... not sure that's quite the same thing exactly, but I see the issue.  Nonetheless, they do work differently.  But when you introduce case that are know to cause errors, such as divide by zero, then all bets are off.

mx
Top Expert 2010

Commented:
I only posted it to show that all arguments in Switch() are going to be evaluated, no matter whether we think they "should" be.  In that respect, the behavior is similar to IIf.Granted, if I use an expression of 1/0, I am asking for trouble.  However, if the expression is ColumnA / ColumnB, who's to say if ColumnB will be zero?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Man memory fails again.  I thought they (Microsoft) had changed that at one point, but I just checked A97, A2000, A2002, A2003, and A2007 and they all do the same.
JimD.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
FUBAR lol.

mx
Stephanie HudsonBusiness Intelligence Developer

Author

Commented:
Anyone know the purpose for this?  I mean, wouldn't it make code a LOT faster if there was "boolean shortcut logic"?

Can the behavior of asking for the parameter be trapped and ignored?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<Anyone know the purpose for this? I mean, wouldn't it make code a LOT faster if there was "boolean shortcut logic"?>>
Since it goes to the internals of VBA, and no one here that I know of has worked directly for Microsoft, then it would be just a guess. Probably has to do with how an expression is passed to the expression service and evaluated.
But as I said, that's only a guess. I would think that if they could change it they would have by now. It's been that way for a long time. And maybe that's where I'm confused; they may have been discussing that at one point and just didn't
<<Can the behavior of asking for the parameter be trapped and ignored?>>
In place of IIF()'s, you can write a function and call that from the query to return the price.
JimD.


Stephanie HudsonBusiness Intelligence Developer

Author

Commented:
Thanks Everyone!  At least I understand now and have some other options.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial