Nested iff in Report 2

[NoviceNYC]

how do i used nested IFF statement in a report?

i have a calculated field in my report (Net Sales %) and I want to create another calculated field (Incentive Earned) using the data from Net Sales % field such that if Net Sales is between 10% and 20%, Incentive Earned is 1% times another field (Net Sales$); if Net Sales is between 20% and 30%, Incentive Earned is 2% times Net Sales $, etc.

The syntax I used is something like...
=IIf([TotalNetSales%]>=0.1<0.2,[TotalNetSales]*1.01)OR IIf([TotalNetSales%]>=0.2<0.3,[TotalNetSales]*1.02)...

 

[PaulBricker]

A nested IIf looks like this.

=IIf([TotalNetSales%]>=0.1 And [TotalNetSales%]<0.2,[TotalNetSales]*1.01,IIf([TotalNetSales%]>=0.2 and [TotlaNetSales%]<0.3,[TotalNetSales]*1.02),IIf([TotalNetSales]>= 0.3 And [TotalNetSales%] < 0.4,[TotalNetSales%] * 1.03,[TotalNetSales%] * 1.04)))


Paul

 

[dhookom]

I would set up an incentive table with the ranges of values and incentives. I would not enjoy maintaining a large IIf() function in one or more areas in my application every time the ranges changed. A nice compromise would be to create a single function that accepts the TotalNetSales% value and returns the incentive.

Duane
MS Access MVP

 

[VBACT]

I agree with Duane. It would be more efficient to create a table with the &quot;ranges&quot; and write a function. Nested IIf functions may be a problem for users to maintain. Also creating these values in a seperate table will allow users to modify/add/delete entries depending on the &quot;commission structure&quot; (which always changes!!!!)

 

[NoviceNYC]

PaulBricker, thank you for your help last week. it worked beautifully. thanks! of course, now i have another question.

How do we avoid #NUM! and #DIV/0! errors in a calculated field? My current syntax looks like this:

=IIf([Sum Of Net Dollars Oct 02 to Dec 02]=0,[4QNI%]=&quot;n/a&quot;,IIf([sum of Net Dollars Oct 03 to Dec 03]=0,[4qni%]=&quot;n/a&quot;,[4QNI%]))

We want to replace the errors with N/A. Many thanks!

 

[PaulBricker]

Can you give me a little more info where you are using this expression.


Paul

 

[NoviceNYC]

Ok, so i have these two columns in my report.
Net Dollars 2002 [Sum of Net Dollars Oct 02 to Dec 02]and Net Dollars 2003 [Sum of Net Dollars Oct 03 to Dec 03].

And then I have a calculated field [Net Increase $] which is the difference between Net Dollars 2003- Net Dollars 2002. And finally I have a calculated field Net Increase % [4QNI%].

When Net Dollars 2002 is $0 and Net Dollars 2003 is $500; my Net Increase % [4QNI%] turns into #DIV/0! to be a number or N/A.

Thanks!

 

[PaulBricker]

My confusion is with the field 4QNI%. Is that the name of the Calculated textbox with the IIF statement?

I would expect the IIF() to look like this.

=IIf([Sum Of Net Dollars Oct 02 to Dec 02]=0,&quot;n/a&quot;,IIf([sum of Net Dollars Oct 03 to Dec 03]=0, &quot;n/a&quot;,[4QNI%]))

Paul

 

[NoviceNYC]

Yes, $QNI% is the calculated text book and yes, you are a genius. Thank you for all your help!

 

[jdegeorge]

Novice...

You should give Paul a star for helping you out, especially since you called him a genius, which fits because it was a great solution. I'll give him one as well.

Jim DeGeorge

 

[NoviceNYC]

Remember I am a NoviceNYC? How does one give a star?

 

[PaulBricker]

Just don't make me live up to that. Thanks for the * Jim.

Paul

 

[jdegeorge]

Sorry for the soapbox!!!

In the bottom of each post is a link that reads:


Mark this post as a helpful/expert post!

Just click it.




Jim DeGeorge