Link to home
Start Free TrialLog in
Avatar of Stephanie Hudson
Stephanie HudsonFlag for United States of America

asked on

IIf - why is it evaluating both responses?

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.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
>>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.

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?
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.
Avatar of Stephanie Hudson

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Everyone!  At least I understand now and have some other options.