Check For Null Value

Hello,

I have to check for a value of NULL in my field and keep getting a syntax error.

What have I done wrong?

=SUM(IIF(Fields!new_status.value IS NULL, CInt(Fields!Total_NotRec.value), 0))

Is this Excel or Access or something?

|||

Look into the IsDBNull() method (instead of using IS NULL). Use IS NULL within a SQL statement to check for null. Use IsNull() within a SQL statement to return a different value if the field is null. Use IsDBNull() within your VB code to check if a database field is null.

Also, if you're using a DataTable there will be a boolean method for each nullable column called Is[ColumnName]Null().

|||

In SRS, they have a function IsNothing.
When I wrapped the IsNothing function around this, I got a sum of 0.

SUM(IIF(Fields!new_rpcstatus.value IS NULL, CInt(Fields!Total_NotRec.value), 0))

Is there any other way to check for the NULL value?

|||

Hi,

From your description, you are unable to calculate the total value

I'm not sure how do you handle with IsNothing function? Have tried the following expression?

=SUM(IIf(IsNothing(Fields!ANYFIELD.Value),0,Fields!ANYFIELD.Value))

OR

=SUM(IIf(Fields! ANYFIELD.Value is nothing, 0, Fields! ANYFIELD.Value))

If the issue still exists, please use IIF function to check each row's value, to see if it matches the conditions.

Thanks.