IIF Statement doesn't return any values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
It looks like you're using a semi-colon as the statement delimiter. IIF
requires a comma.

HTH,
Barry
 
Back
Top