Query to not display records over 3 hours old

[funwithaccess]

Hi everyone,

Can anyone guide me on how to build a query in Access 2010/13 that will not display any records that are older than 3 hours? See below for more specifics.

The criteria would be based on a field that houses the time (End Time) and also a field that houses a number (Status). So, only if the field in the record says 3 (based on the Status field) then it should not display the record after 3 hours (based on the End Time field) of being changed to a 3. Is it possible to do that? I have attached a screenshot to give an idea of what I am referring to.

Side note, this forum is always top notch! Thanks in advance.

Nate

 

[pr2-eugin]

This,

(based on the End Time field) of being changed to a 3. Is it possible to do that?

suggests that you are trying to do a calculated field in the table is that right? If so I would advice you not to do it.

PS: Yes you can do what you want to do. But be a little bit clear of the exact requirement, as in what data would you like to see in the output based on the dataset you furnished.

 

[funwithaccess]

This,suggests that you are trying to do a calculated field in the table is that right? If so I would advice you not to do it.

PS: Yes you can do what you want to do. But be a little bit clear of the exact requirement, as in what data would you like to see in the output based on the dataset you furnished.

Thanks for the quick reply, Paul! I would like to display all records other than those that have "3" in the Status field that are older than 3 hours. In other words, after someone changes the Status field to "3" and types in the time in the End Time field(from a form) the query will not display the entire record after 3 hours while still displaying all records that have the Status field filled in with 1 or 2 (you can only use 1, 2, or 3). I want the table to remain untouched, other than adding/editing the records via an append query attached to the form.

Edit: All of this would be in an independent query, not the mentioned append query or table. This query would be solely used to view the data from the table.

 

[funwithaccess]

Ok, so I assume that I would use something like:

Between Time() And Time()-#3:00:00 AM#

This would need to be without the "AM/PM", it would need to be based on hours not time.

I would need to add in criteria based on another field, "Status". If that field has the number "3" then the "End Time" field would be set to not display the record with "3" after 3 hours.

If Status changed to 3 Then Record disappears after 3 hours of being displayed.

Does this make sense?

 

[SparklyPrincess6969]

You could try

Between Now() and (Now() - 3)

Now() returns the current date and time. I believe (-3) on its own will work because Access assumes you mean hours, however you might have to end up playing around with the DateDiff function.

 

[CJ_London]

You will find it much easier if you combine your startdate and time to one field and the same with your enddate and time. If you need to display them separately this can easily be done on your form or report.

In your sample data ID=1 has no end date but does have an end time - is this valid?

I would like to display all records other than those that have "3" in the Status field that are older than 3 hours. In other words, after someone changes the Status field to "3" and types in the time in the End Time field(from a form) the query will not display the entire record after 3 hours while still displaying all records that have the Status field filled in with 1 or 2

Can you clarify what you mean by 'older than 3 hours'. What if a user changes the status to 3 but does not complete an end time? And if there is no end date, how are you determining which date the end time relates to?

the basic criteria you would use is

WHERE Status<>3 OR (Status=3 AND ([End Date]+[End Time])>dateadd("h",-3, now()))