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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy