Prev: Random Selection of Records
Next: Edit and save query criteria
From: csandbrook on 11 Mar 2010 11:12
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)))))


--
csandbrook
From: KARL DEWEY on 11 Mar 2010 12:19
>>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?

--
Build a little, test a little.


"csandbrook" wrote:

> 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)))))
>
>
> --
> csandbrook
From: John Spencer on 11 Mar 2010 14:14
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

csandbrook wrote:
> 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)))))
>
>
 | 
Pages: 1
Prev: Random Selection of Records
Next: Edit and save query criteria