SSRS Expression for DateAdd to return value and blank

Publish date: 2024-05-08

enter image description here

Hi all, I have an expression in SSRS that calculates the Due date from the Last Fit Test. The data is correct if the Last Fit Test has a date. However, if the Last Fit Test is Null or blank, it gives an error "########" (it supposes to return empty or blank). How can I fix that expression?

=IIF(IsNothing(Fields!LastFitTest.Value) = True,"",DateAdd(DateInterval.Year,1,Fields!LastFitTest.Value)) 

1 Answer

Try this ...

=IIF( IsNothing(Fields!LastFitTest.Value), "", DateAdd(DateInterval.Year,1,IIF(IsNothing(Fields!LastFitTest.Value), '1900-01-01', Fields!LastFitTest.Value)) ) 

The problem was the IIF always evaluates both the true and false condition so when you had no value in LastFitTest the false side could not evaluate even though it would never be used.

All we have done here is made the date that get processed in the DateDiff function always return a valid date. The 1st Jan 1900 means nothing, you can put any date you like in there as it will never be used in the final result.

The only other change was to remove the = True. IsNothing returns True/False so you don't have to explicitly say =True.

0

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobWxtaGuGdIKOrKqrq12axbG%2BxKyqoqeeYrOwvoydmK2dkZmxbsDOZqmerKWnu27CwKWsnmWRo7FursuapaQ%3D