We help IT Professionals succeed at work.

IIf - why is it evaluating both responses?

1,389 Views
Last Modified: 2013-11-27
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

CERTIFIED EXPERT
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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 / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

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 / Application Developer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Stephanie HudsonBusiness Intelligence Developer

Author

Commented:
Thanks Everyone!  At least I understand now and have some other options.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.