Hello,

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)

Any ideas?

Teresa

Hi Teresa
You need to use DateAdd. You can check it in Access help but 3 days back would be DateDiff('d', -3, now())

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?

Hi

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

**untested

Create a query:

Select * From tblYourTableName
Where [Status] = "open" And DateDiff("d", [Date Entered], Date()) >= 3

/gustav

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.

Tested this and it works:




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.