July 13, 2011 at 12:22 pm
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?
July 13, 2011 at 12:47 pm
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))
July 13, 2011 at 12:47 pm
why don't you use the 1900-01-01 instead of the zero and then filter out that date if needed.
July 13, 2011 at 2:55 pm
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.
July 14, 2011 at 7:13 am
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.
July 14, 2011 at 8:31 am
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