473,320 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Time Field 24hr query.

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()

9 3071
ajalwaysus
266 Expert 100+
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
@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
ajalwaysus
266 Expert 100+
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
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
ajalwaysus
266 Expert 100+
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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

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

Similar topics

2
by: David B | last post by:
Is it possible to format a time field to the 24hr clock. Been looking through help and it seems to be suggesting that the machine settings are followed, but I don`t want the machine on 24hr ! TIA...
4
by: Mark Reed | last post by:
Hi all, I have a query (query1) which shows scan date, scan time & operator. One scan = 1 record. What I want to do is create a report based on query 2 from query1 which shows all the scans AND...
0
by: Miranda Evans | last post by:
I noticed several postings about issues related to "run-time error 3061", and I observed that the solutions to these issues appear to involve correcting something within the SQL code. I'm...
5
by: james | last post by:
Banging my head here. I have an older Database that I have imported to Access and the original database system (Dataflex) stored the Time of Day in Seconds. I can use...
1
by: ApexData | last post by:
Hello Any know how to convert AM/PM Time to 24Hr Time ? Sample Please ! With and without Now(). ThankYou Greg
6
by: D | last post by:
Hello all...I have an issue with one of my java script functions that I'm hoping someone can easily help with. I have a web based application that we use to create/sign up for overtime. When we...
5
by: Killer42 | last post by:
Hi all. I have a query which calculates the difference between two date/time fields. This returns a number which is, of course, useless without formatting (“I couldn’t believe it took...
23
by: tatata9999 | last post by:
Hi, What time zones tend to use 24 hours time format? Googling hasn't been able to answer the question. Thank you.
3
by: klaydze | last post by:
hi everyone, i know this is easy to others but i'm going to explode now to this. the scenario is this. i would like to trace where the time is equivalent to 1st Shift, 2nd Shift and 3rd Shift where...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.