Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare Dates (A 97)

    I have three date fields on a form: ReceiptDate, InitialResponseDate, FinalResponseDate, CurrentRevisionDate

    On my report I'd like to be able to compare the latter three date fields and choose the most current (or greatest), then subtract the first date field from the result.
    (ex. ReceiptDate = 12/01/02 , InitialResponseDate = 01/01/03 , FinalResponseDate = 02/01/03 , CurrentRevisionDate = 03/01/03 ....so the return should be 03/01/03 - 12/01/02, or roughly 91 days).

    What's the syntax for comparing these three dates in the Field: row in a QBE window?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare Dates (A 97)

    Something like:

    TurnaroundTime: IIF([CurrentRevisionDate]>[FinalResponseDate],[CurrentRevisionDate]-[ReceiptDate],[FinalResponseDate]-[ReceiptDate])

    ?

    If the dates are not real date fields, you might have to put cdate() around them to perform the calculation.

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare Dates (A 97)

    Cecilia,

    That works great, but how do I incorporate the InitialResponseDate bit in here...can I nest another IIF or IF stmt within the IIF stmt you provided?

    I guess I should have clarified that two of the three date fields are independent of each other (ie. FinalResponseDate doesn't necessarily have a corresponding InitialResponseDate that preceeds it, but a CurrentRevisionDate WOULD have to have either an IntitialResponseDate or a FinalResponseDate preceeding it. The process is such that a "Final" response can be issued without the need for an "Initial" response first.)

    And the fields are true date fields, so luckily I won't have to convert....


    Thanks,

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare Dates (A 97)

    You can nest a bunch of if statements if you need to. Another way to do it is you can do a domain aggregate function in the query. It's slow, but I do it when there's no other way to get what I want. This assumes that your dates are in a table. It would look like this:

    MaxDate: DMax("MyDateField","tblDateTable")

    I'm not sure how you'd do it in your program, but you just have to get creative.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare Dates (A 97)

    Am I correct that not all three of the latter dates need to occur in a single record, but if present, FinalResponseDate will not be earlier than InitialResponseDate, and CurrentRevisionDate will not be earlier than InitialResponseDate or FinalResponseDate? In that case, you can use the following expression:

    IIf(IsNull([CurrentRevisionDate]),IIf(IsNull([FinalResponseDate]),[InitialResponseDate],[FinalResponseDate]),[CurrentRevisionDate])-[ReceiptDate]

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare Dates (A 97)

    Hans,

    You are correct in that assumption...the expression you provided works great.
    Thanks to both you and Cecilia for the assistance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •