1

I am using below expression in SSRS but is not giving expected result.

=IIf(Fields!Completed_Date.Value Is Nothing,
    Nothing,
    IIf(First(Fields!TIMEFORMAT.Value,"dsPreferences") = True,
        Format(DateAdd("n",Parameters!UTCOffset.Value,Fields!Completed_Date.Value),"MM/dd/yyyy hh:mm:ss tt"),
        Format(DateAdd("n",Parameters!UTCOffset.Value,Fields!Completed_Date.Value),"MM/dd/yyyy HH:mm:ss")
    )
)

It has to check if Date is null.If it is null just leave field blank.If it is not null then it has to return value according to Time zone value that it is getting from another dataset.This expression works fine when there is non null date but it returns #error when there is Null date.

tezzo
  • 10,858
  • 1
  • 25
  • 48
Mandar
  • 369
  • 2
  • 5
  • 11

1 Answers1

1

Try using this syntax:

=IIf(IsNothing(Fields!Completed_Date.Value), "", <yourFalsePart>)

This syntax probably doesn't solve your issue because Completed_Date is also used in the false part. IIf operator will always evaluate both expressions before deciding which one to use: so if Completed_Date is Nothing it will broke your expression anyway.

Try using a Custom Function as explained here: SSRS expression giving error with iif condition

Community
  • 1
  • 1
tezzo
  • 10,858
  • 1
  • 25
  • 48
  • What you have tried? Using "" as True Part? Because using IsNothing instead of your Is Nothing is not the solution: it is only a matter of preferences. :) – tezzo Oct 01 '14 at 14:26
  • Yeah, i tried putting "" as a true part.it works perfectly fine when there is date but problem with null date.when i try just with string for testing purpose in false part it worked but with column value it gives problem. – Mandar Oct 01 '14 at 14:37
  • ok, so the problem is the good old 'IIF operator will always evaluate both expressions before deciding which one to use'! – tezzo Oct 01 '14 at 14:44
  • So how to control this behavior to let it evaluate only one and then display result without evaluating second one. – Mandar Oct 01 '14 at 16:09