troubleshooting Question

IIf - why is it evaluating both responses?

Avatar of Stephanie Hudson
Stephanie HudsonFlag for United States of America asked on
Microsoft AccessSQL
13 Comments5 Solutions1508 ViewsLast Modified:
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.
Join our community to see this answer!
Unlock 5 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros