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 RecordsNext: Edit and save query criteria