How do I get this expression to show a 0(Zero) if its NULL


  • Hi,

    I have a expression which does the following...

    =iif(InStr(Fields!val.Value.ToString,".") <> 0,FormatCurrency(Fields!val.Value),FormatNumber(Fields!val.Value,0))

    But in some cases if nothing exists I am gettin and #Error in that field.. How do I get rid of that #Error and insert a 0 or 0.00

    Any help will be appreciated.



    Tuesday, April 10, 2012 7:13 PM


All replies

  • Karen,

    Option 1: In the SQL statement set the default value for the “val” field (or nullable fields), example ISNULL(val,0)

    Option 2:  Use IIF(IsNothing(Fields!val.Value),0, Fields!val.Value) expression instead of Fields!val.Value

    =iif(InStr(IIF(IsNothing(Fields!val.Value),0, Fields!val.Value.ToString),".") <> 0,FormatCurrency(IIF(IsNothing(Fields!val.Value),0, Fields!val.Value)),FormatNumber(IIF(IsNothing(Fields!val.Value),0, Fields!val.Value)))

    My TFS Blog
    Please remember to mark as answered, if this reply helps

    Tuesday, April 10, 2012 8:44 PM
  • Hi All,

    It is very simple expression.

    You just need to write.

    =IIF(Fields!val.Value Is Nothing,0.00,Fields!val.Value)

    Thats it!!



    Wednesday, April 11, 2012 8:35 AM
  • Tried both your solutions and still getting the error...

    My sql procedure is getting one value called Val... and when i try to format it to currency and Number thats when its giving me the #error

    Wednesday, April 11, 2012 1:09 PM
  • Check data type of your Value returned by a procedure.

    Wednesday, April 11, 2012 1:43 PM
  • it is a Float

    and when i am inserting data to that table I am doing a count(column) name

    and sum(column) + adding a 0.0001 to it

    Wednesday, April 11, 2012 2:08 PM
  • Try

    =IIF( (IsNothing(Fields!val.Value)), True,False)


    Wednesday, April 11, 2012 2:44 PM
  • I am trying to write an expression that would format a number and currency and if nothing exists i want a 0 instead of a #Error
    Wednesday, April 11, 2012 2:49 PM
  • i agree with Adhi answers instead of using instr use CDbl() to convert into a double it might work


    Wednesday, April 11, 2012 3:22 PM
  • no luck
    Wednesday, April 11, 2012 3:43 PM
  • Hi Karen,

    Please try the expression:
    =IIF(IsNothing(Fields!val.Value), 0, FormatCurrency(Fields!val.Value))

    If you have any questions, please feel free to let me know.

    Mike Yin

    Thursday, April 12, 2012 9:19 AM
  • and sum(column) + adding a 0.0001 to it

    If you are doing the above operation in cell, try this:

    =IIF(IsNothing(sum(Fields!New2.Value)), 0, CDbl(sum(Fields!New2.Value)+0.0001))

    Thursday, April 12, 2012 9:46 AM
  • Ended up creating a calculated field and added this


    is nothing ,0, ( switch(instr(fields!val.Value.tostring,".") <> 0 and IsNothing(Fields!val.Value) = false, FormatCurrency(Fields!val.Value) ,instr(fields!val.Value.tostring," ") = 0 and IsNothing(Fields!val.Value) = false,FormatNumber(fields!val.Value,0))))
    Thursday, April 12, 2012 6:42 PM