Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Comparing Date Fields (2000 - SR2)

    I have a query which contains three date fields. One is called [DueDate], the other [ExtensionDate] and the third is [ClosedDate]. I need to know how many of the related records are "Closed" on time. The common element is that the [DueDate] and [ClosedDate] are required on every record, and I can quickly identify how many records were closed "on or before the" the stated [DueDate]. The problem comes in to play when an [ExtensionDate] is noted. Extension Dates are not used on every record, but when they are used then that date becomes the new [DueDate]. However, we still need to capture or retain the original [DueDate] so that we can identify the total time (date) element of how long it takes to complete the task.

    The problem I am having is that I do not know what criteria to put in my query to compare the [CloseDate] to both the [DueDate] and [ExtensionDate], and to allow for instances where the [ExtensionDate] is Null. Any ideas.

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

    Re: Comparing Date Fields (2000 - SR2)

    Try this criterion for ClosedDate: < = Nz([ExtensionDate],[DueDate])

    Note: I put spaces between < and = and Nz. This is because some browsers display something else if there is no space between < and =. Don't include the spaces in your query.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Comparing Date Fields (2000 - SR2)

    Thanks Hans. That did it.

Posting Permissions

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