Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How do I get this expression to show a 0(Zero) if its NULL

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

  • Tuesday, April 10, 2012 7:13 PM
     
      Has Code
    Sign In to Vote
    0

    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.

    Thanks

    Karen

     

All Replies

  • Tuesday, April 10, 2012 8:44 PM
     
     
    Sign In to Vote
    0

    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)))

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

     
  • Wednesday, April 11, 2012 8:35 AM
     
     
    Sign In to Vote
    0

    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!!

    Thanks,

    Amey

     
  • Wednesday, April 11, 2012 1:09 PM
     
     
    Sign In to Vote
    0

    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:43 PM
     
     
    Sign In to Vote
    0

    Check data type of your Value returned by a procedure.

     
  • Wednesday, April 11, 2012 2:08 PM
     
     
    Sign In to Vote
    0

    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:44 PM
     
     
    Sign In to Vote
    0

    Try

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

    Pradnya07

     
  • Wednesday, April 11, 2012 2:49 PM
     
     
    Sign In to Vote
    0
    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 3:22 PM
     
     
    Sign In to Vote
    0

    i agree with Adhi answers instead of using instr use CDbl() to convert into a double it might work

    Pradnya07

     
  • Wednesday, April 11, 2012 3:43 PM
     
     
    Sign In to Vote
    0
    no luck
     
  • Thursday, April 12, 2012 9:19 AM
    Moderator
     
     
    Sign In to Vote
    0

    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.

    Regards,
    Mike Yin

     
  • Thursday, April 12, 2012 9:46 AM
     
     
    Sign In to Vote
    0

    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 6:42 PM
     
     Answered
    Sign In to Vote
    0

    Ended up creating a calculated field and added this

    =iif(fields!val.Value

    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))))