Can't get rid of #Error result

  • Hi

    I'm working with a DateDiff expression that is calculating the difference between two date field values. When there is not a date for those fields it returns #Error. So, I put in an IIF to check the fields and return a zero if the dates don't exist. Every combination I've tried will not work. The DateDiff still works fine but it won't put a zero in the field. Here's the expression I've been working with....

    =IIF(Len(CStr(Fields!Course_Start_Date.Value))<1,0,DateDiff("ww",Fields!Course_Start_Date.Value,Fields!Course_End_Date.Value))

    I put the CStr in there in case it was having problems with the date formatting. (and we aren't consisten with date formatting in our data source :ermm: ) I've tried all kinds of variants on that theme and no luck. I also tried a Switch to see if that would work and got the same results.

    =SWITCH(LEN(Fields!Course_Start_Date.Value)=0,"0",LEN(Fields!Course_Start_Date.Value)>0,DateDiff("ww",Fields!Course_Start_Date.Value,Fields!Course_End_Date.Value))

    So....Anyone have any ideas?

  • I am assuming the issue is with the iif statement, not the DateDiff function. Instead of doing a length comparison, can you try using the IsNothing function. I've found this to be a better way to check for no value.

    =IIF(IsNothing(Fields!Course_Start_Date.Value),0,DateDiff("ww",Fields!Course_Start_Date.Value,Fields!Course_End_Date.Value))

  • why don't you use the 1900-01-01 instead of the zero and then filter out that date if needed.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • I did try the IsNothing. I found two forms of that, the one you posted and another that looked like this:

    IIF(Fields!Course_Start_Date.Value is Nothing,.......

    Unfortunately, neither of those did the trick.

    I have not tried the 1900-01-01. I'll have to give that a try.

  • I don't think the switch function has a functional else. Maybe it is just the way I do it and I have forgotten, but I believe the switch function has to have pairs.

    SWITCH(TestCondition1, Output1, TestCondition2, Output2, TestCondition3, Output3)

    not

    SWITCH(TestCondition1, Output1, TestCondition2, Output2, Output3)

    If you don't have a 3rd test condition you can use to get your else output

    SWITCH(TestCondition1, Output1, TestCondition2, Output2, True, Output3)

    If I am reading your expression correctly, you are using a functional else, rather than a pair for the last output. Try using a pair.

  • Another thing to check, it looks like you're only performing comparisons for the start date length = 0. Could it be the end date that is throwing the #error? You could probably check both dates using the SWITCH you have been playing with. Something like:

    SWITCH(LEN(Fields!Course_Start_Date.Value)=0,"0",LEN(Fields!Course_End_Date.Value)=0,"0",1=1,DateDiff("ww",Fields!Course_Start_Date.Value,Fields!Course_End_Date.Value))

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply