IIf statement 1

[debbiecarvery]

Hi,

I would like to add a field to a report that calculates a bonus.

If the employee has between 1000 and 1499 points they get $8.00

If the employee has between 1500 and 1999 points they get $12.00

and if the empoyee has over 2000 points the get $16.00.

Here is what I tried entering in my expression builder

=IIf([Sum Of Total Points] Between "1000" And "1499","8","0&quot Or ([Sum of Total Points] Between "1500" And "1999","12","0&quot Or ([Sum of Total Points]>"2000","16","0&quot

...it is not working can someone help me out as to what I am doing wrong ?

Thanks,
Debbie

 

[scriverb]

Because you more than one condition to your expression the Switch statement seems more appropriate. Also, I am making the assumption that the fields that we are reviewing here are numeric and not text so I have removed the quote marks so that numeric comparisons can be made.

=Switch([Sum Of Total Points] >=1000 And <= 1499,Format(8,&quot;$0.00),[Sum Of Total Points] >=1500 And <= 1999,Format(12,&quot;$0.00), [Sum Of Total Points] >=2000,Format(16,&quot;$0.00), [Sum Of Total Points] <= 999 ,Format(0,&quot;$0.00))

Bob scriver

 

[jdegeorge]

You want to nest this like:

=IIf([Sum Of Total Points] Between 1000 And 1499,8,IIf([Sum Of Total Points] Between 1500 And 1999,12,IIf([Sum Of Total Points]>=2000,16,0)))

Notice that I removed the quotes from around your numbers. If they're numeric values you don't need the quotes.

This should work.

Let me know.

Jim &quot;Get it right the first time, that's the main thing...&quot;

 

[debbiecarvery]

Thank You, I tried Jim's and it worked, thank you both

Debbie

 

[scriverb]

They both will work, I had a missing closing quote around the &quot;$0.00&quot; in the Format statements. The advantage of the Switch is that you have an unlimited number of conditions that can be checked. They logic just keeps checking the next one and so on down through all of the expressions and returning the value if true. If nothing comes up true then a Null is returned which can be controlled with an NZ function around the whole thing.

=Switch([Sum Of Total Points] >=1000 And <= 1499,Format(8,&quot;$0.00&quot,[Sum Of Total Points] >=1500 And <= 1999,Format(12,&quot;$0.00&quot, [Sum Of Total Points] >=2000,Format(16,&quot;$0.00&quot, [Sum Of Total Points] <= 999 ,Format(0,&quot;$0.00&quot)

Glad you found your answer.

Bob scriver

 

[jdegeorge]

Debbie:

Always glad to help. Happy to hear it worked.


Bob:

I'm liking the Switch() function. Never used it before. Very nice alternative to my old friend IIF().


Jim &quot;Get it right the first time, that's the main thing...&quot;