0

I am dividing Field_A by Field_B. But the problem is that sometimes Field_A is NULL (doesn't have any value). How can i check for that?

If it is Null, i guess the result should be 0 or Null.

xorpower
  • 17,975
  • 51
  • 129
  • 180

3 Answers3

1

Use an IIF (inline If statement) in the expression:

=IIF(<Condition>,<TruePart>,<FalsePart>)

=IIF(Field_A Is Nothing OR Field_B = 0, 0, Field_A/Field_B)

This way, you are making sure that Field_A is not null and Field_B is not zero, to also avoid divide by zero errors.

dev_etter
  • 1,156
  • 13
  • 32
  • 2
    If there is any possibility that Field_B will be 0, then you should avoid this answer. SSRS IIF does not perform Short Circuit evaluation. All parts of the IIF are evaluated for any input, so if Field_B is 0, then you will get an exception `#ERROR` – Jamie F Feb 20 '12 at 14:49
  • Thanks, it skipped my mind to work in the divide by zero fix. Answer has been edited. – dev_etter Aug 29 '12 at 18:26
  • I'm not seeing the divide by 0 fix applied here - it should be after the /, not before, e.g. http://stackoverflow.com/a/19190408/1787137 – Mike Honey Sep 25 '15 at 00:47
  • The IIF condition checks for Field_B having a value of 0. If Field_B is zero, it does not perform the calculation. Instead, it returns 0. – dev_etter Sep 25 '15 at 14:44
0

The Easiest Method is to simply add a very small value to potential null value/zero value So :

=IIF(Field_A Is Nothing OR Field_B = 0, 0, Field_A/( Field_B + .000001))

This will still give you 0 when the value is either null or zero, but will prevent the #error from showing up when the denominator is otherwise null or zero

Also : Be careful to make sure that your types are the same; meaning, if the data type of your Field_A is decimal or double, make sure that you cast the small amount .000001 to CDec(.000001)

I have found that making sure that all types in your expression are the same is key to avoiding the #Error message

novell morris
  • 221
  • 2
  • 3
-1

Yes, I think what "Jammie F" has commented above is correct.If Field B is 0 then you will get Divide by zero error. What you can use is:-

iif(Field_B=0,0,Field_A/Field_B)

anakulk
  • 196
  • 1
  • 6