Using If Statment in reports 1

[paul123456]

I have a report based as a letter. I am trying to get an $ amount figure based upon a record amount number. for example if the amount on a record is 200.00 or less then i would like to display 20 dollars. and if the amount on the record is 201.00 or greater and less then 1000.00 then i would like to display 75.00 dollars. this is going to be used as a fee based upon the dollar amount in the amount field. i would like to do a if statement saying if the [amount.table] is less then 200.00 then display 20 dollars. if it is more then 200.00 and less then 1000.00 then display 75.00 dollars. any ideas on how this could be done?

Thanks, PAUL

 

[PaulBricker]

In the control source for a textbox you can use
=IIf([RecordAmount]< 200, 20.00,IIf([RecordAmount]<1000, 75, 100))

You don't say what to do if it is over 1000 so I just put in 100 as an arbitrary value. You don't have to fill in that argument but this structure should get you close.

Paul

 

[paul123456]

Thnx for the reply Paul. Do you think that i can somehow do this IIf statement within the text field? i have a bunch of text in a text field and i want it to look like a paragraph. IE
&quot;you will be reimbursed in the amount of &quot; & IIf([RecordAmount]< 200, 20.00,IIf([RecordAmount]<1000, 75, 100)) &quot; & from your original amount of &quot; & [RecordAmount] & &quot;.&quot;

is there a way to do this? because if i add another text in the paragraph if it grows to much im afraid that it will overlap the next paragraph and im worried about the spacing between words.

Thanks, PAUL

 

[PaulBricker]

You should be able to use it exactly as you have it as the Control Source for your textbox except you need to add an = sign to the front of it
=&quot;You will be reimbursed i the amount of &quot; & IIf([RecordAmount]< 200, 20.00,IIf([RecordAmount]<1000, 75, 100)) &quot; & from your original amount of &quot; & [RecordAmount] & &quot;.&quot;



Paul

 

[paul123456]

My mistake was after the 100)) i had the quote in the wrong place. now when i view the report it shows up as a default of 75. i have been going through a bunch of records and found one that was 16,345 and it showed up as 125? all the record amount 5000 or less show up as 75 here is my code i am trying to use. if you get a chance take a look at it. Does it matter if the if my recordamount record is in the currency value? does it need to be converted to number?

=&quot;You will be reimbursed i the amount of &quot; & IIf([RecordAmount]<0,24,IIf([RecordAmount]<4999,75,IIf([RecordAmount]<9999,95,IIf([RecordAmount]<14999,125,IIf([RecordAmount]<20000,195))))) & &quot; from your original amount of &quot; & [RecordAmount] & &quot;.&quot;


Thanks, PAUL

 

[PaulBricker]

Sorry, missed that. Nice catch. I added an n to the word IN that was missing and took out one IIf statement and just left the last argument for values > 14999.

=&quot;You will be reimbursed in the amount of &quot; & IIf([RecordAmount]<0,24,IIf([RecordAmount]<4999,75,IIf([RecordAmount]<9999,95,IIf([RecordAmount]<14999,125,195)))) & &quot; from your original amount of &quot; & [RecordAmount] & &quot;.&quot;

If you want to change that, you can just add the last IIf([RecordAmount} < 20000,195) back in. It shouldn't matter is the amount is formatted currency.

Paul

 

[paul123456]

Thnx for catching that. hehe. Ok i made the last iff statement the IIf([RecordAmount]<14999,125,195)))). here is exactly what i have now

=&quot;You will be reimbursed in the amount of &quot; &

IIf([RecordAmount]<0,24,IIf([RecordAmount]<4999,75,IIf([RecordAmount]<9999,95,IIf([RecordAmount]<14999,125,195))))

& &quot; from your original amount of &quot; & [RecordAmount] & &quot;.&quot;

for some reason and i have been playin with this and can't figure it out.. everything under 5000 defaults to 75 here are some of the numbers i get

if the RecordAmount is 7,588.50 I get 95
if the RecordAmount is 14,425.35 i get 125
if the RecordAmount is 23,898 I get 195

it seems to be one group higher in the amount.

here is what i am trying to achieve

5000 or less = 24
5000 - 10000 = 75
10000 - 15000 = 95
15000 - 20000 = 125
20000 > = 195

I will play with this a little more, i wonder if I should start from the left and do a less than statement starting with the highest amount?

thank you for your help

Thanks, PAUL

 

[PaulBricker]

It's doing just what we told it to so here's what you actually need for the IIf statement.
IIf([RecordAmount]<5000,24,IIf([RecordAmount]<1000,75,IIf([RecordAmount]<15000,95,IIf([RecordAmount]<20000,125,195))))

This should do it.

Paul

 

[paul123456]

Your good! thnx..the greater than less than statement was confusing. it works great!!!!! thnx again paul

Thanks, PAUL