displaying records for a certain amount of time that has elapsed

[Chanchito]

hi there,

I am seeking some guidance in regards to creating a query. I would
like to be able to have the query display records that have had a
certain amount of time pass since the time that is listed a particular
field of the record. There is a field labeld DispTime. This field is
updated with the current time whenever data is typed into another
field on the current record. What I am trying to figure out is how to
create a query that would go through all the records for the day and
list the ones that have had 2 hours pass since the time listed in the
DispTime field. I am using 24hour time. So if it was 10:00 hours and
there were about 5 records whose DispTime field contains 07:00 hours
the query would be able to bring up those 5 records since they are the
only ones who have had two hours pass since the time in their DispTime
field.

That sounds confusing doesn't it? hehe. I apologize ahead of time. =)

[Bas Cost Budde]

Chanchito wrote:

hi there,

I am seeking some guidance in regards to creating a query. I would
like to be able to have the query display records that have had a
certain amount of time pass since the time that is listed a particular
field of the record. There is a field labeld DispTime. This field is
updated with the current time whenever data is typed into another
field on the current record. What I am trying to figure out is how to
create a query that would go through all the records for the day and
list the ones that have had 2 hours pass since the time listed in the
DispTime field. I am using 24hour time. So if it was 10:00 hours and
there were about 5 records whose DispTime field contains 07:00 hours
the query would be able to bring up those 5 records since they are the
only ones who have had two hours pass since the time in their DispTime
field.

That sounds confusing doesn't it? hehe. I apologize ahead of time. =)

You want all records that are at least two hours old.

Is that all? hehe. Just nagging Chanchito, please don't take this as an
insult.

Is DispTime a Date/Time field? And do you write Now() in it? In that
case the date is stored with the time, and you can do a simple subtract.
Time is stored as fraction. So two hours would mean a difference of at
least 2/24.

Another way is using datediff("h",disptime,now())

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[mybrainhurts]

Bas Cost Budde wrote:

You want all records that are at least two hours old.

Is that all? hehe. Just nagging Chanchito, please don't take this as an insult.
np. I realize that it does sound quite rudimentary, but my mind doesn't
seem to be cooperating with me today. =)

Is DispTime a Date/Time field? And do you write Now() in it? In that
case the date is stored with the time, and you can do a simple subtract. Time is stored as fraction. So two hours would mean a difference of at least 2/24.
yes, it is a date/time field, with SHORT TIME format. The field is
updated with NOW() when another field is updated with data. I will see
what I can do with your suggestion of the Subtract using fractions for
time.

Another way is using datediff("h",disptime,now())

I tried the DateDiff("h", [DispTime], Now()) but it brought back a
strange value. The disptime was 13:07 and when I ran the query at 15:01
it brought back a diff value of 920186. Not sure what that value is in
regards to time and hour.
However, i will keep plugging away at it. Thanks again.
chanch.

[Bas Cost Budde]

my**********@gmail.com wrote:

np. I realize that it does sound quite rudimentary, but my mind doesn't
seem to be cooperating with me today. =)
I had my private grin when you were done with your description and I
finally understood the question.

Then again, understanding the question is most of the time a large step
towards an answer.
yes, it is a date/time field, with SHORT TIME format. The field is
updated with NOW() when another field is updated with data. I will see
what I can do with your suggestion of the Subtract using fractions for
time.
I have the feeling that DateDiff is just a formatter for this
subtraction. If I query for now()-disptime, I get values like 0,09. You
could use this, multiplying by 24 to get hours into the Integer realm;
if the difference is >2, you have a hit.

That is:

(now() - [disptime])*24 > 2
I tried the DateDiff("h", [DispTime], Now()) but it brought back a
strange value. The disptime was 13:07 and when I ran the query at 15:01
it brought back a diff value of 920186. Not sure what that value is in
regards to time and hour.

Huh? What a strange value. But if your dates are off (mmm... using only
Now?) that may explain it a bit.

I didn't test this on values in a table, with a query, on my first
answer; now that I did, strangely enough, Access (my version at least)
insists in rewriting my "h" into "\h" giving me the impression something
is wrong.

Well, what the heck: two hours is 120 minutes, so try "n" in the
DateDiff. Or stick with arithmetic.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea