Post reply

Advice with an expression please

  • June 30, 2015 at 8:00 am

    #318639

    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

    #1809146

    Replace the date field(s) with another IIF to return the default date if the date is blank.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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

    #1809156

    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

    #1809198

    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"))


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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

    #1809200

    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

    #1809215

    Dear Alvin

    Thanks, your solution worked perfectly. I wish I could buy you a drink.

  • June 30, 2015 at 10:45 am

    #1809216

    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

    #1809238

    faulknerwilliam2 (6/30/2015)

    Dear Alvin

    Thanks, your solution worked perfectly. I wish I could buy you a drink.

    You're welcome. Drink sounds good right now. 🙂


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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