Time Field 24hr query.

[Rotorian]

Good Morning,

I have a table which has a "Date" field and a "Time" field. The "Date" field is format "Short Date" with a default value of "Date()" The "Time" field is format "Short Time" and the default value is "Time()".

I am trying to query for records created in the past 24hrs by using the following query:

SELECT TableLogEntries.Acft, TableLogEntries.Date, TableLogEntries.Time, TableLogEntries.Comment
FROM TableLogEntries
WHERE (((TableLogEntries.Time)>=-24));

Unfortunatly this returns all records with 24 or less in the time field, not the past 24hrs records. So, I am missing something in the WHERE part, correct?. I am searching, just haven't found something close to try.

[ajalwaysus]

You need to add a part for Date.
Along the lines of this..

Date = Now()-1

-AJ

[Rotorian]

You need to add a part for Date.
Along the lines of this..

Date = Now()-1

-AJ

Okay, when I run this

SELECT TableLogEntries.Acft, TableLogEntries.Date, TableLogEntries.Time, TableLogEntries.Comment
FROM TableLogEntries
WHERE (((TableLogEntries.Time)Date = Now()-1));

I get an error Syntax error (missing operator) in query expression. I think I plugged in your suggestion in the wrong spot.

[ajalwaysus]

OK you misunderstood, you need to think along the lines of using the date field and time fields together. Filtering by date/time is complicated and needs a solid understanding before you can use it with ease, use this link to get you started.
The best way to solve this is probably by combining the Date and Time fields together because you want the last 24 hours which most likely goes between 2 days which is why you need the date, not just the time.

WHERE ((TableLogEntries.Date & ' ' & TableLogEntries.Time) >= Now() -1) AND ((TableLogEntries.Date & ' ' & TableLogEntries.Time) <= Now())

This code should give you everything that occurred between exactly one day ago from the time you run this to today's date and time.

Personally Date/Time still screws me up sometimes, so I welcome anyone else's input.

Let me know if you have any question,
-AJ

[Rotorian]

That worked.

Let me asked this from a DB design PoV is it better to have two fields, one for date and the other for time? Or one field with both items in it? I.e. instead of having two fields, one with short date and the other with short time, just have one field with General date and time format? Would the query be simpler then, or would it still remain the same?

[ajalwaysus]

The query would be simpler, because then you can break them apart if needed, but from the form point of view, it depends:
1. If this is just for viewing purposes, then one field with Date and Time together is the best way to go, and easiest.
2. If this form is for data entry, then I would suggest keeping them separate, because it is harder to enforce that someone enter a time when entering a date unless you keep them separate.

-AJ

[NeoPa]

I would use Between, and I would certainly use a Date/Time field rather than two separate (Date & Time) fields for this (If data verification is required it's perfectly acceptable to have two separate controls on a form but join the two values into a single field).

Assuming a Date/Time field (called DateTime) we would use :

WHERE TableLogEntries.DateTime Between Now()-1 And Now()

[Rotorian]

Odd thing happened today ajal's suggestion stopped working.

So I went ahead and deleted the time field and used a single field with date "General Format" and used NeoPa's suggestion and is working. I wonder what change, probably impossible to determine.

[NeoPa]

To be fair to AJ, his suggestion was more because he was trying to match your spec as closely as possible. I'm sure were he left to his own devices he would have suggested something along very similar lines to what I did.

I can't think why it may have gone wrong suddenly mind you. Hard to tell without a little more detail I would guess. Anyway, it's very good news that everything is now working soundly.

[Rotorian]

Hiya Neo,

Ya wish i had more detail to give you, but i dont. The query itself stopped working. At first I thought it was the form, so I ran the query on its own and nothing was being returned. Just plain odd. I even deleted Ajal's code and re typed it and nothing. Anyway, all is good, and is a better design, one less field to worry about :).