Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    14

    Query to pull data with dates greater than 5 days from add date

    Having a pickle of a time with this and need some help!

    PROBLEM:
    I have created a MSAccess database to track workflow. I need to generate a list that shows all the items that have a status as 'open' and greater than 5days.

    EXAMPLE:
    START DATE STATUS
    2010.06.01 Open (should appear in list)
    2010.06.01 Closed (should not appear in list)
    2010.06.05 Open (should appear in list)
    2010.06.17 Open (should not appear in list, but when I run the report on
    06.23, and it's still open, it should then appear).

    Hope this makes sense. Help please! Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Code:
    SELECT [start date], status
    FROM myTable
    WHERE status = "open" OR [start date] BETWEEN DATEADD("d", -5, DATE()) AND DATE()

  3. #3
    Join Date
    Aug 2009
    Posts
    14
    Thanks, but it's still not working the way I want it. Here's my SQL code:

    SELECT [Policy Tax Workflow].[Policy No#], [Policy Tax Workflow].Status, [Policy Tax Workflow].[Type Of Complaint], [Policy Tax Workflow].[Assigned To], [Policy Tax Workflow].[Date Received]
    FROM [Policy Tax Workflow]
    WHERE ((([Policy Tax Workflow].Status)="Open") AND (([Policy Tax Workflow].[Date Received]) Between DateAdd("d",-5,Date()) And Date()));

    I have 4 items in my table. 3 have a DATE RECEIVED date of 06/16/2010 and 1 has a date of 06/01/2010. I would expect the query to return just the one result.

    Any ideas?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    I would expect that query to return three results. But I think I understand what you mean by greater than 5days now. Change the BETWEEN to <= and remove the AND DATE().

  5. #5
    Join Date
    Aug 2009
    Posts
    14
    SUCCESS! Thanks Pootle Flump! Much appreciated!

  6. #6
    Join Date
    Aug 2010
    Location
    Virginia
    Posts
    1
    Thanks that helped me as well, it was just what I was looking for.

Posting Permissions

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