CODEWITHSUNDEEP
sqlreporting-servicesssrs-2008datediffiif
Kendall I.
Kendall I.

Reputation: 43

How to Fix #Error When Calculating a DateDiff With an IIF Statement in SSRS

I'm creating a report in Reporting Services to display time-spans for classroom sessions in our program. One column that was requested is to show the total number of hours between the start and end time of one session. I used a DATEDIFF function showing the DateInterval in hours as the total hours in one time-span, but I get a #Error returned for the blank values that haven't been assigned a begin or end time.

I added an IIF statement to mark the values that are blank as "TBA", but that didn't work. Since I'm casting a string to a datetime value, I enclosed the DATEDIFF statement in a CStr function, but that also isn't working. Lastly, I did the simple thing and set the text box to display a Number value since it's returning the total number of hours, but of course, that didn't work either. My code is as follows:

=IIF(Fields!BeginTime.Value = "", "TBA", CStr(DateDiff(DateInterval.Hour, Fields!BeginTime.Value, Fields!EndTime.Value)))

It's supposed to show that "TBA" value in the blank spots, but I still get a #Error. The total hours are coming up fine for the values that aren't blank, however. I checked the value in the dataset to see if it's returning a blank or a NULL-labeled value, and it is returning a blank value.

Upvotes: 1

Views: 1197

Answers (1)

Steve-o169
Steve-o169

Reputation: 2146

I believe the issue is arising in the conditional statement. If Fields!BeginTime.Value is a date datatype, comparing it to "" will likely result in the error you're getting. I suggest using the IsNothing function or just checking if the field is nothing. The two options are as follows.

=IIF(IsNothing(Fields!BeginTime.Value), ...

Or...

=IIF(Fields!BeginTime.Value Is Nothing, ...

With that said, you shouldn't need to use the CStr as DateDiff will return an integer value. Your textbox should be set to Default or some text variant to prevent errors there. The expression will return either an integer or string so a number format on the textbox will not work.

Additionally, if you're having the same issue when there is no EndTime, you may want to add an OR to the conditional to fix that.

 =IIF(IsNothing(Fields!BeginTime.Value) OR IsNothing(Fields!EndTime.Value), ...

Upvotes: 1

Related Questions