The added or subtracted value results in an un-representable datetime

Question

I have the following expression to check parameter date values and replace from date if necessary:

=IIF(Parameters!FromDate.Value is nothing, 
    IIF(Parameters!ToDate.Value is nothing,
        DateAdd("d",-30,Now()),
        DateAdd("d",-30,Parameters!ToDate.Value)),
    Parameters!FromDate.Value)

Only when the todate is null, an error will get appear:

The added or subtracted value results in an un-representable datetime.

Did anybody face such a problem?

Please help us repro your issue: provide sample values for the parameters where you get this problem. — Jeroen, Sep 08 '14 at 07:32
Here is the case, when the fromdate parameter has value I want to use it to filter the data. but in case the FromDate is null, Then I should look at ToDate parameter. if for example the ToDate = "2014-09-04" then the FromDate should be "2014-08-04" otherwise the from date should be "2014-08-09"(30 days before today). As I searched about this problem there is something wrong with the DateTime Type. I guess a datetime value will not be null at any time, even when we tick allow null values. — Leila, Sep 08 '14 at 23:27

score 2 · Accepted Answer · answered Oct 09 '14 at 03:40

The problem is that IIF is a function, not a language construct so it evaluates both parameters before passing them to the function. That means DateAdd("d", -30, Parameters!ToDate.Value) gets evaluated even when Parameters!ToDate.Value is Null, thus giving you this error.

Try this instead:

=IIF(Parameters!FromDate.Value is Nothing, 
    DateAdd(DateInterval.Day, -30, IIF(Parameters!ToDate.Value is nothing, Today, Parameters!ToDate.Value)),
Parameters!FromDate.Value)

The added or subtracted value results in an un-representable datetime

1 Answers1