June 30, 2015 at 8:00 am
Hello
I am trying to write an expression in a calculated field that shows if one date value is greater than another:
=(IIF(fields!date1.value>fields!date2.value,"late","on time")
This works fine when there is a value in fields!date2.
However, if fields!date2.value is blank, the expression returns "on time". I don't want it to do this, as date1 may be 1/4/15 - if date2 is blank, that mean's it's overdue and I therefore want the calculated field to say "late".
Therefore I want the expression to say "late" if fields!date1.value is < today's date (basically, if it's blank and fields!date1.value is before today's date, then it means it's overdue).
Please can anyone advise on how to amend the expression to accommodate this?
Thanks
June 30, 2015 at 8:08 am
Replace the date field(s) with another IIF to return the default date if the date is blank.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 30, 2015 at 8:37 am
Thanks for the very prompt response: much appreciated.
Sorry to appear feeble, but could you please suggest how I would write the expression as you mention above?
June 30, 2015 at 9:56 am
faulknerwilliam2 (6/30/2015)
Thanks for the very prompt response: much appreciated.Sorry to appear feeble, but could you please suggest how I would write the expression as you mention above?
If I understand your requirements correctly, I think what you're looking for is something like the code below. You may have to replace fields!date2.value = "" with something else depending on data type, or whether the data is blank versus null. You may also have to convert Today() to another data type.
=(IIF(fields!date1.value > IIF(fields!date2.value = "", Today(), fields!date2.value),"late","on time"))
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 30, 2015 at 10:01 am
You need something like the following (this won't work as written, but it should give you an idea):
=(IIF(fields!date1.value>IIF(fields!date2.value = "", Today(), fields!date.value),"late","on time"))
For example, you may need to use the IsEmpty function and Today() may not be the correct function to get the date.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 30, 2015 at 10:43 am
Dear Alvin
Thanks, your solution worked perfectly. I wish I could buy you a drink.
June 30, 2015 at 10:45 am
Dear Jack
Thanks very much for taking the trouble to get back to me. I tried Alvin's solution first, which worked, but yours looks very similar and I'm sure would have done the trick.
Many thanks.
June 30, 2015 at 11:25 am
faulknerwilliam2 (6/30/2015)
Dear AlvinThanks, your solution worked perfectly. I wish I could buy you a drink.
You're welcome. Drink sounds good right now. 🙂
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply