3

I am creating a table in SSRS with Business Intelligence 2008. I have a date, as a string, as one of the values used in the table. This value may have a string representing a date, OR it could also be blank. If it has a value, I want the value formatted in a different way. Right now, I have this expression for the cell in which it is displayed:

=IIf(Fields!MyDate.Value = Nothing, "", Format(CDate(Fields!MyDate.Value), "dd-MM-yyyy"))

This works perfectly if the field has a value. However, when the field is blank, the cell is populated with #Error. This would make sense if I just had the Format function, but it seems like the IIf should prevent it from trying to run that with a blank value. I tested it with this expression:

=IIf(Fields!MyDate.Value = Nothing, "No value", "Value")

...and sure enough the blank values entered the correct part of the statement and printed "No Value". I don't understand why the second part of the expression, which isn't used, would cause an error. Is there a way to rewrite this that will not cause an error? Thanks

EDIT: As I mentioned in the comments, I also tried a switch statement like this:

=Switch(Fields!MyDate.Value = Nothing, "", Fields!MyDate.Value <> Nothing, Format(CDate(Fields.MyDate.Value), "dd-MM-yyyy"))

However, this returned #Error for blank values as well.

EDIT Regarding Possible Duplicate: PLEASE do not mark this as a duplicate to the question asking whether IIf short-circuits. I looked there, it asks a different question and does not give an answer that works for what I need.

thnkwthprtls
  • 3,287
  • 11
  • 42
  • 63
  • did you tried using the `IsNothing` function instead?: `IIf(IsNothing(Fields!MyDate.Value), "No value", "Value")` – Lamak May 17 '16 at 18:02
  • Have you tried just setting the format string for the field instead of using `IIF`? – D Stanley May 17 '16 at 18:03
  • 1
    @Lamak The problem is that IIF does not short-circuit, so it errors when evaluating the second option. – D Stanley May 17 '16 at 18:04
  • Possible duplicate of [Does the iif function compute both paths in SSRS or is it short-circuited?](http://stackoverflow.com/questions/1204179/does-the-iif-function-compute-both-paths-in-ssrs-or-is-it-short-circuited) – Jamie F May 17 '16 at 18:08
  • D Stanley, Thanks I didn't realize it did that, I fguess in that case what I need is a way to get it to only run the Format statement if it's a valid value, and skip it otherwise. Jamie F, that isn;t what I was asking, I wasn't asking whether it does that or not, I'm asking how to make it avoid running what would be an invalid command. I forgot to mention (I'll add it above too), I also tried a switch statement, and that returned an error as well – thnkwthprtls May 17 '16 at 18:10

1 Answers1

6

The problem is that Iif does not short-circuit. One ugly workaround is to use a nested Iif:

=IIf(Fields!MyDate.Value = Nothing, "", _
    Format(CDate(Iif(Fields!MyDate.Value = Nothing, Today(),Fields!MyDate.Value)), "dd-MM-yyyy"))

Which is ugly, repetetive, and error-prone.

Another option is to create a custom function that does short-circuit:

Public Function FormatOrBlank (ByVal theDate As DateTime, ByVal format As String)
If String.IsNullOrWhiteSpace(theDate) 
  Return ""
Else
  Return Format(CDate(theDate)), format)
End If
End Function
D Stanley
  • 149,601
  • 11
  • 178
  • 240