Filtering last 24 hours Records

[Craig Fellows]

Hello,

I have a query that collects data based upon date and
time. I can drop the date element if I can always obtain
the last 24 hours worth of records.

Our database records contain a time that is keyed in
manually (it's not the time the record was entered, it
relates to something else).

Essentially I want to query the last 24 hours records at
any point in time. So even at just after midnight the
query will still pull the appropriate records.

If anyone can help it would be much appreciated.

 

[Jeff Boyce]

Craig

Into what kind of field is your "time" keyed? A text field, a date/time
field, ...?

One approach would be to use a query to add the date of the record and the
time-value of the record together, then search for records with this
combined date/time within 24 hours of your comparison point-in-time.

The way you'd "add these together" will vary, depending on how you've
recorded the time...

 

[Guest]

Jeff,

Thanks for your quick reply.

The "time" field is a date/time, when the time is input
into the database it goes like this: 00:00:00 AM (example
12:12:12 AM).

Please forgive me Access experience is relatively nothing
so hence why I'm struggling with this. Also I'm not sure
how I would go about performing the other suggestions you
made, such as joining together etc.

Thanks

 

[Jeff Boyce]

Craig

Even though you might not realize it, the "date/time" field you are using to
store the time includes a date. The data underlying the field is stored as
a decimal number -- the "whole" number portion is the number of days since
sometime over a century ago (if you MUST know, it's available somewhere),
while the decimal fraction portion is the decimal fraction of a day
representing the hours/minutes (i.e., noon is .5000).

If you added the "date only" portion of your data (say, 6/3/2004) and the
"time only" portion of your data (say "noon"), you'd get a date/time field
that could be displayed (formatted) as "6/3/2004 12:00:00 PM".

If you are trying to compare date/time information from your records to a
given date/time (e.g., Now()), you could use a query to "build" the
comparison date/time.

Take a look at Access HELP on the DateAdd() and DateDiff() functions, as
well as the DateValue() and TimeValue() functions. You'd use these within a
query to "build" your combined date/time.