Iif Statement Problem

[csandbrook]

I am trying to run the below query to simply find data in a column that is a
certain criteria, then taking data in another column and multiplying it and
it is not returning the correct amounts based on the below. Can someone help
me to write this correctly. Thank you.

Adjusted DR Payout: IIf([Rep % of Average]<80,[REP DR PAYOUT]*0.8,IIf([Rep %
of Average]<90.01 And [Rep % of Average]>79.99,[REP DR PAYOUT]*0.9,IIf([Rep %
of Average]<105.01 And [Rep % of Average]>90.01,[REP DR PAYOUT]*1,IIf([Rep %
of Average]<115.01 And [Rep % of Average]>105.01,[REP DR PAYOUT]*1.1,IIf([Rep
% of Average]>115.01,[REP DR PAYOUT]*1.2,0)))))

 

[KARL DEWEY]

it is not returning the correct amounts based on the below.
You did not say what it was doing that was unexpected. Is it returnning any
value? Can you work the math backwards and determine what is wrong?

What kind of field is [Rep % of Average]? What are examples of values it
contains?

 

[John Spencer]

You can shorten the test by using something like the following

Adjusted DR Payout:
IIf([Rep % of Average]<80,[REP DR PAYOUT]*0.8
,IIf([Rep % of Average]<90.01,[REP DR PAYOUT]*0.9
,IIf([Rep % of Average]<105.01,[REP DR PAYOUT]*1
,IIf([Rep % of Average]<115.01 ,[REP DR PAYOUT]*1.1
,IIf([Rep % of Average]>=115.01,[REP DR PAYOUT]*1.2,0)))))

If Rep % of Average is actually a percentage then you need to move the decimal
points since 80% is .8, 90.1% is .901, etc.

IIf([Rep % of Average]<.80,[REP DR PAYOUT]*0.8
,IIf([Rep % of Average]<.9001,[REP DR PAYOUT]*0.9
,IIf([Rep % of Average]<1.0501,[REP DR PAYOUT]*1
,IIf([Rep % of Average]<1.1501 ,[REP DR PAYOUT]*1.1
,IIf([Rep % of Average]>=1.1501,[REP DR PAYOUT]*1.2,0)))))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County