Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Measuring Three Date (2000 SR-2)

    I am using an expression in a query to measure the number of days from one date to another. The fields are related to a corrective action style report where a DueDate is established, and then the actual CloseDate is captured at the appropriate time. Using this expressions I am able to calculate the number of "overdue" days for those items closed late.

    Overdue: [CloseDate]-[DueDate]

    It works great. However, there is an allowance for an ExtensionDate to be granted. That date is always later then the original DueDate and is entered in it's own separate field [ExtDate]. The problem I am running into is how to subtract the CloseDate from either the DueDate or ExtDate, provided the ExtDate was granted. In many cases an extension is not requested so there are many time that field is null. How do I get the expression to recognized the latest date between the DueDate and ExtDate, and to take into account that the ExtDate may be null (the DueDate is always required).

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Measuring Three Date (2000 SR-2)

    Try
    Overdue: [CloseDate]-nz([ExtDate],[DueDate])

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Measuring Three Date (2000 SR-2)

    Great. Thanks Doug.

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Measuring Three Date (2000 SR-2)

    Doug, one other issue has come up. In many cases if the CloseDate is prior to the actual DueDate or ExtDate it results in a (-) number. When I total all of the number up to calculate how many of the corrective actions were overdue, and by how many days total, and how many days average, the (-) number sqews the final numbers. How can I translate the (-) number into a "0" so it doesn't affect the calculated numbers.

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

    Re: Measuring Three Date (2000 SR-2)

    In one column:

    Overdue: IIf([CloseDate]<Nz([ExtDate],[DueDate]),0, [CloseDate]-Nz([ExtDate],[DueDate]))

    Or in two columns:

    Diff: [CloseDate]-Nz([ExtDate],[DueDate])
    Overdue: IIf([Diff]<0,0,[Diff])

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Measuring Three Date (2000 SR-2)

    Thanks. That works great.

Posting Permissions

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