May 11 2012, 08:12 PM
I have a table that has a "Date Entered" field that enters the date entered automatically. I also have a status field that defaults to "open" when the record is entered and will be changed to "complete" when the isssue is resolved.
I have been asked to create a query to show only the records that have the status "open" and the date entered is >= 3 days (basically to show any record that is still open 3 or more days after it was entered)
It seems like a simple filter, but I don't know exactly how to do it. I can succesfully filter date()-3 to return open records three days less than today's date.... but I don't know if that is the same... and then what about the records 4 days or 5 days ago??
I have not been able to generate any results keying from the "Date Entered" field. What am I missing (my thought was [Date Entered]=>3.... but not working)
May 11 2012, 09:08 PM
You need to use DateAdd. You can check it in Access help but 3 days back would be DateDiff('d', -3, now())
May 12 2012, 08:46 AM
Thank you for your reply.
Please help me understand where I am supposed to enter your suggested DateDiff('d', -3, now())
Do you mean that I enter DateDiff('d', -3, now()) in the query criteria to filter the records? (when I put <Date()-2 in this line the query returns any records that were entered 3 day before the current date)
When I put your suggested DateDiff('d', -3, now()) in the criteria line nothing returns
If I create a calculated field it returns a date, but I am trying to filter my records
What am I missing?
May 12 2012, 09:31 AM
Give this a try - in query design view, in the criteria row of your status field put "Open", then create a new query field
and in the criteria row of that new field put >=0
May 12 2012, 09:35 AM
Create a query:
Select * From tblYourTableName
Where [Status] = "open" And DateDiff("d", [Date Entered], Date()) >= 3
May 12 2012, 04:41 PM
In your query for status field use criteria "open" and create a new field( i.e expr1:Date()-[DateEntered]) with criteria >=3. I think it will work.
May 12 2012, 07:20 PM
Tested this and it works:
SELECT tblIssues.IssueID, tblIssues.IssueEnteredDate
Create a new query and go to SQL view. Paste in the code and change it to your own table and field names. I used tblIssues for the table name and IssueID as the primary key and IssueEnteredDate as the two fields. Once you look at it in design view it should make more sense.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here