IIF Statement doesn't return any values

[Guest]

I'm using below expression to calculate the revenue of a given volume.
However I can't get Access to calculate accoring to below. The IIf statement
works in the sense that when the volume is below Volume Level 5 the
expressions returns 0 but otherwise it doesn't return anything. Is there
anything apparently wrong with this expression?

=IIf([Vol 2006 Q1]>[Volume Level 5];((([Vol 2006 Q1]-[Volume Level
5])*[Price Interval 5-])+(([Volume Level 5]-[Volume Level 4])*[Price Interval
4-5])+(([Volume Level 4]-[Volume Level 3])*[Price Interval 3-4])+(([Volume
Level 3]-[Volume Level 2])*[Price Interval 2-3])+(([Volume Level 2]-[Volume
Level 1])*[Price Interval 1-2])+(([Volume Level 1])*[Price Interval 0-1]));0)

As I will continue to build such expressions over a period of time to
calculate volume discounts I also wonder if there are any limitations in
Access with regard to this that I should aware about.

Many thanks in advance / Johan Belfrage

 

[Guest]

In your formula, if any of the fields value is Null, it will return Null.
If that the case try replacing the Null with 0, and see what happen

=IIf([Vol 2006 Q1]>[Volume Level 5];(((Nz([Vol 2006 Q1],0)-Nz([Volume Level
5],0))*nz([Price Interval 5-],))+((Nz([Volume Level 5],0)-Nz([Volume Level
4],0))*nz([Price Interval 4-5],0))+((nz([Volume Level 4],0)-Nz([Volume Level
3],0))*nz([Price Interval 3-4],0))+((nz([Volume Level 3],0)-nz([Volume Level
2],0))*nz([Price Interval 2-3],0))+((nz([Volume Level 2],0)-nz([Volume
Level 1],0))*nz([Price Interval 1-2],0))+((nz([Volume Level 1],0))*nz([Price
Interval 0-1],0)));0)

 

[Barry Gilbert]

It looks like you're using a semi-colon as the statement delimiter. IIF
requires a comma.

HTH,
Barry