SSRS trouble using DateDiff in a IIF statement

Question

SSRS returns an #error when the statement is False, but No Start Date when the statement is True. I tested and the DateDiff is correct in the False side of the statement. I'm not sure which direction to go from here. Thank you.

=IIF(Fields!JobOrderIssueDate.Value = "", "No Start Date",DateDiff("d",Today(),Fields!ProjectedConstEndDate.Value))

Answer 1

You are mixing data types in your IIF returning either a STRING or a DATE depending on the condition of the IIF. This is not going to work.

One approach would be to convert the data type returned by the FALSE condition so that it is also returns a STRING (using CStr()):

=IIF(Fields!JobOrderIssueDate.Value = "", "No Start Date",CStr( DateDiff("d",Today(),Fields!ProjectedConstEndDate.Value)))

or you could gain a little more control over formatting (using FormatDateTime()):

=IIF(Fields!JobOrderIssueDate.Value = "", "No Start Date",FormatDateTime( DateDiff("d",Today(),Fields!ProjectedConstEndDate.Value), DateFormat.ShortDate))