none
Expression divide and returning #error. RRS feed

  • Question

  • Expression divide and returning #error. How do i get my code to return zero if multiplying by 0? It works fine if there is a value other than zero.  

    =(Fields!Principal.Value /ReportItems!Textbox211.value)


    Monday, July 15, 2019 10:26 PM

Answers

  • Hi g_cat,

    There are a number of ways to avoid divide-by-zero errors, but essentially it comes down to this:  you must check if your denominator is equal to zero or null before you perform the operation.  Failure to do this will result in #Error(s).

    Here's how I do it:  Since this is such a common problem in reporting systems, I have my own DIVIDE function:

    Public Shared Function Divide(Num1 as double, Num2 as double) AS object
    
    	IF 	IsNothing ( Num1 ) or IsNothing (Num2) or Num2 = 0 Then
    		Divide = Nothing
    	Else
    		Divide = Num1 / Num2
    	End If
    
    End Function

    I call this function like this:  =Code.Divide(Fields!Principal.Value, ReportItems!TextBox211.Value)

    The above function call performs the following checks:  If Num1 is NULL or Num2 is NULL or Num2 is zero, return NULL (Nothing).  If you prefer divide by zero to return zero (instead of Nothing), replace the Nothing with 0
    Otherwise, perform the division.

    To use this function go to Report-->Properties-->Code and drop the code into the box

    Then in your text box, enter this expression:  =Code.Divide(Fields!Principal.Value, ReportItems!TextBox211.Value)

    I hope this helps!

    --Dan

    • Marked as answer by g_cat Wednesday, July 17, 2019 4:13 PM
    Wednesday, July 17, 2019 3:30 PM

All replies

  • Hi g_cat

    According to your description , seems you have use the reportitems expression in your expression .

    As far as I know, if you use the reprotitems expression , seems you could not use the aggerate function for it .

    So you could not use the iif function to avoid the zero condition.

    If possible do not use the reprotitems!textbox.value in your expression , and then use the iif function to avoid the zero contidion .

    Or you could offer the detailed expression about the Reportitems!Textbox211.value and the table structure to us for more further research .

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Tuesday, July 16, 2019 1:40 AM
  • How do i get my code to return zero if multiplying by 0?

    You can check the value with an Iif Expression:

    =Iif(ReportItems!Textbox211.value = 0, 0, (Fields!Principal.Value /ReportItems!Textbox211.value))


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, July 16, 2019 5:51 AM
  • Wednesday, July 17, 2019 2:22 PM
  • Wednesday, July 17, 2019 2:27 PM
  • Olaf,

    I tried the code you suggested.  It worked up to a point we are forecasting out 20, 30 years.  It worked up to 2048,but it stopped working after that point in time.  

    Wednesday, July 17, 2019 2:33 PM
  • Is there an if zero nothing if expression I can use? 
    Wednesday, July 17, 2019 3:11 PM
  • Hi g_cat,

    There are a number of ways to avoid divide-by-zero errors, but essentially it comes down to this:  you must check if your denominator is equal to zero or null before you perform the operation.  Failure to do this will result in #Error(s).

    Here's how I do it:  Since this is such a common problem in reporting systems, I have my own DIVIDE function:

    Public Shared Function Divide(Num1 as double, Num2 as double) AS object
    
    	IF 	IsNothing ( Num1 ) or IsNothing (Num2) or Num2 = 0 Then
    		Divide = Nothing
    	Else
    		Divide = Num1 / Num2
    	End If
    
    End Function

    I call this function like this:  =Code.Divide(Fields!Principal.Value, ReportItems!TextBox211.Value)

    The above function call performs the following checks:  If Num1 is NULL or Num2 is NULL or Num2 is zero, return NULL (Nothing).  If you prefer divide by zero to return zero (instead of Nothing), replace the Nothing with 0
    Otherwise, perform the division.

    To use this function go to Report-->Properties-->Code and drop the code into the box

    Then in your text box, enter this expression:  =Code.Divide(Fields!Principal.Value, ReportItems!TextBox211.Value)

    I hope this helps!

    --Dan

    • Marked as answer by g_cat Wednesday, July 17, 2019 4:13 PM
    Wednesday, July 17, 2019 3:30 PM
  • Thank you! It worked !   
    Wednesday, July 17, 2019 4:13 PM