By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Time Field 24hr query.

P: 23
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT TableLogEntries.Acft, TableLogEntries.Date, TableLogEntries.Time, TableLogEntries.Comment
  2. FROM TableLogEntries
  3. WHERE (((TableLogEntries.Time)>=-24));
  4.  
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.
Oct 7 '09 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE TableLogEntries.DateTime Between Now()-1 And Now()

Share this Question
Share on Google+
9 Replies


Expert 100+
P: 266
You need to add a part for Date.
Along the lines of this..
Expand|Select|Wrap|Line Numbers
  1. Date = Now()-1
-AJ
Oct 7 '09 #2

P: 23
@ajalwaysus
Okay, when I run this

Expand|Select|Wrap|Line Numbers
  1. SELECT TableLogEntries.Acft, TableLogEntries.Date, TableLogEntries.Time, TableLogEntries.Comment
  2. FROM TableLogEntries
  3. 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.
Oct 7 '09 #3

Expert 100+
P: 266
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.

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 7 '09 #4

P: 23
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?
Oct 7 '09 #5

Expert 100+
P: 266
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
Oct 7 '09 #6

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE TableLogEntries.DateTime Between Now()-1 And Now()
Oct 7 '09 #7

P: 23
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.
Oct 10 '09 #8

NeoPa
Expert Mod 15k+
P: 31,494
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.
Oct 10 '09 #9

P: 23
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 :).
Oct 10 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.