Error level and expressions

In a report, I have the expression :

=iif(Sum( Fields!X_A_1.Value)<>0,(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value),"NA")

This expression product the error

"The value expression for the textbox ‘textbox12’ contains an error: Attempted to divide by zero."

I think it's because reporting execute the expression
(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value)

even if X_A_1 is equal to 0.

I would like reporting to display NA and not "#Error" when X_A_1 = 0

Is there is a way to catch the error in reporting?

Is there is a way for reporting not to execute the expression

(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value)

when X_A_1 is equal to 0.

Sorry if my english is not very good

Note: IIF() is a function call and evaluates all arguments (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp)

Instead of the pattern =IIF(Y=0, A, X/Y), use =IIF(Y=0, A, X / IIF(Y=0, 1, Y)) to avoid the division by zero.

-- Robert

Error in SQL Query

Hi,

I am trying this query
rsOFF.Open "SELECT SUM(ISNULL(OFF_QUANT,0)*ISNULL(OFF_PRICE,0)) AS tot FROM dbo_OFF_ITEM WHERE DB_CONTRACT=" & Form_dbo_AE_CONTRACT1.DB_CONTRACT, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic

but is is giving me this error:
Wrong number of argument used with function in query expression SUM(ISNULL(OFF_QUANT,0)*ISNULL(OFF_PRICE,0))

I could not find error.

can anyone help me.Not sure what is causing the error, but I doubt that you need the ISNULL functions anyway. If either value is Null then the product will be null and won't roll up into the SUM anyway. Aggregate functions ignore NULLs, so they are mathematically equivalent to zeros for summation.

Try simplifying your query statement to this, and see if it does not solve the problem:

rsOFF.Open "SELECT SUM(OFF_QUANT*OFF_PRICE) AS tot FROM dbo_OFF_ITEM WHERE DB_CONTRACT=" & Form_dbo_AE_CONTRACT1.DB_CONTRACT, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic