How to add in IF ERROR

• hazeleyre_23

  SSC-Addicted

  Points: 449

  More actions

  February 8, 2016 at 3:48 am

  #324637

  Hi,

  How do I add IIF error in the following statement? As it is currently coming up with NaN, as there is no data in the file yet, therefore I just want it to show 0 rather than NaN.

  =SUM(Fields!TC.Value AND Fields!Den.Value)+SUM(Fields!TCO.Value AND Fields!Den.Value)+SUM(Fields!TCCF.Value AND Fields!Den.Value)

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 8, 2016 at 4:24 am

  #1857082

  Is that in an MS Access form/report?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• hazeleyre_23

  SSC-Addicted

  Points: 449

  More actions

  February 8, 2016 at 4:29 am

  #1857084

  I am using SSRS.

  thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738140

  More actions

  February 9, 2016 at 8:24 am

  #1857391

  What's "it"?

  Here is a ref for the IIF() statement: https://msdn.microsoft.com/en-us/library/ms157328.aspx

  I'm not sure what values aren't coming up as you expect, but if you have a problem with TC.Value not being returned correctly?, you can use this:

  =IIF( ISNothing(Fields!TC.Value), 0, SUM(Fields!TC.Value AND Fields!Den.Value)+SUM(Fields!TCO.Value AND Fields!Den.Value)+SUM(Fields!TCCF.Value AND Fields!Den.Value))

  The way this works is you use an

  =IIF(test condition, return if true, return if false)

  Add other IIF expressions to test your values. Or use an AND with a few to ensure that all your return values are present.