Is it possible to use DateDiff in an IIF statement?

Question

I have searched looking for help but can't find exactly what I need. I am trying to find the total number of days between two date fields, however the initiating date fields could be from two different fields, eg Start Date, or amended Start Date. The end date will always be the same field.

The majority of the time there will not be an amended start date but I need a way to cater for the scenario that an start date has been amended. I tried the following

=IIF(Fields!AmendedStartDate.Value is nothing, DateDiff("d",Fields!StartDate.Value,Fields!EndDate.Value, DateDiff("d",Fields!AmendedStartDate.Value, Fields!EndDate.Value)))

I get an error run a run this.

I am fairly new to Report Builder/SSRS so I am unsure if what I am asking for is even possible.

Thanks for taking the time to look.

Answer 1

You can't do Fields!FieldName.Value is nothing, instead you need IsNothing(Fields!FieldName.Value):

=IIf(IsNothing(Fields!AmendedStartDate.Value),DateDiff("d",Fields!StartDate.Value,Fields!EndDate.Value),DateDiff("d",Fields!AmendedStartDate.Value,Fields!EndDate.Value))

Alternatively, you could create a Calculated Field (called say, StartDateToUse) with a similar expression:

=IIf(IsNothing(Fields!AmendedStartDate.Value),Fields!StartDate.Value,Fields!AmendedStartDate.Value)

And then refer to this field in your main expression to get the result you want:

=DateDiff("d",Fields!StartDateToUse.Value,Fields!EndDate.Value)