If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Query to pull data with dates greater than 5 days from add date

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
King of Understatement
 
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()
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old
King of Understatement
 
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().
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2009
Posts: 14
SUCCESS! Thanks Pootle Flump! Much appreciated!
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2010
Location: Virginia
Posts: 1
Thanks that helped me as well, it was just what I was looking for.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On