Time criteria in a query (1 Viewer)

[shamal]

Welcome
How can you create criteria in a query to show data at the current time, or at the current hour, or an hour before now, or two hours ago, or to show data during a specific period of time?

 

[Pat Hartman]

For the current time and a time range you would use the actual field.

Where YourTime = Now() --- keep in mind that this is very unlikely to work due to the extended decimal places you get with some times so formatting both would be necessary.
Where Format(YourTime, "hh:nn:ss") = Format(Now(), "hh:nn:ss")

Where YourTime Between(StartTime and EndTime)

For Current Hour or +- some number of hours, you need to extract just the hour part. Keep in mind that the +- won't work if the times can cross dates. You would need to use a range that includes a full datetime to cross midnight.

Where Hour(YourTime) = Hour(SomeOtherTime)
Where Hour(YourTime) = Hour(SomeOtherTime) + 3
Where Hour(YourTime) = Hour(SomeOtherTime) -1

You might want to print this out and keep it handy. VBA has lots of functions that are useful and the list by category makes them easy to find.

Access Functions (by category) - Microsoft Support

Find Help with Access functions in this categorical list.

support.microsoft.com

 

[shamal]

The steps did not work!!

 

[Gasman]

What are the chances of you having a record with that?
Now

? now()-4
18/12/2023 19:22:07

Your query ?

SELECT Table3.Ide, Table3.Time
FROM Table3
WHERE (((Table3.Time)=Now()-4));

 

[Pat Hartman]

1. That bears no resemblance to anything I suggested.
2. When you simply subtract a number from a date, the assumption is that the number is "days". Therefore, your expression is subtracting 4 DAYS from Now(), not 4 hours.
3. There is no such think as a Time data type. Therefore, anything defined as a date data type includes a date. That date will default to 12/29/1899 because dates are stored as double precision numbers NOT strings and they represent the number of days since 12/29/1899 (SQL Server uses a different origin date and I think Excel might also). So some date formatted as 10:10:32 is actually 12/29/1899 10:10:32 AM.

 

[Pat Hartman]

@shamal "liking" my post isn't helpful. If the post helped to solve the problem, it is better to be specific. That way, others who read the post will know what actually worked.

 

[shamal]

@shamal "liking" my post isn't helpful. If the post helped to solve the problem, it is better to be specific. That way, others who read the post will know what actually worked.

The problem has not been solved yet!

 

[moke123]

In furtherence of Pats point #3, you can extract the time portion from the date and time with TimeValue()

?timevalue(#12/23/2023 11:54:44 AM#)
11:54:44 AM

you need the date portion however as pat pointed out.

 

[Pat Hartman]

The problem has not been solved yet!

We are not mind readers. The code you posted did not reflect my advice. What did you change it to?

 

[shamal]

We are not mind readers. The code you posted did not reflect my advice. What did you change it to?

The criteria was added to the query but it is not working properly!

​

 

[Gasman]

I have already pointed out your critera is incorrect in a big way.

The criteria is not the issue, you can just use Between two values.
The hard bit IMO is how you decide what those two values are?

Once you determine those values, the rest is easy.

 

[Pat Hartman]

Sorry, I should have recommended TimeValue() as @moke123 mentioned


SELECT Table3.Ide, TimeValue([Time]) AS Times
FROM Table3
WHERE (((TimeValue([Time]))>=TimeValue(DateAdd("h",-4,Now()))));

However, this won't work if the times cross midnight.

 

[Gasman]

Here is your criteria

? Format(DateAdd("h",-4,Now()),"hh:nn AM/PM")
01:39 PM

Here is your data from that query
I have no idea why there is so much space

Your query

SELECT Format([Time],"hh:nn AM/PM") AS Times, Table3.Ide
FROM Table3
WHERE (((Format([Time],"hh:nn AM/PM"))>=Format(DateAdd("h",-4,Now()),"hh:nn AM/PM")));

when it should be more like

SELECT Table3.Time, Table3.Ide
FROM Table3
WHERE (((Table3.Time)>=Format(DateAdd("h",-4,Now()),"hh:nn AM/PM")));

or

SELECT Table3.Time, Table3.Ide
FROM Table3
WHERE (((Table3.Time)>=TimeValue(DateAdd("h",-4,Now()))));

 

[Pat Hartman]

My original use of Format() was triggered by the = request since that gets rid of all the extra decimal places you get with the date data type and which can easily keep 12:10:13 from not being equal to 12:10:13 because we're looking at a formatted time (always) but Access is looking at the double precision value that represents that time. But, once you use any other operand, you run into the problems caused by making the time a string and so would need to format based on the hierarchy of the parts AND use a 24 hour clock.

This should show the problem. On the first line, I printed out the actual double precision value of Now(). On the third line, I converted it back to a date and printed it as a human would view the data. Notice the 5th and 7th lines where I slightly modified the time part and it still = 1:03:10 PM which is why you can almost never get an = result when comparing to Now()

print cdbl(now())
45283.5438657407
print cdate( 45283.5438657407)
12/23/2023 1:03:10 PM
print cdate( 45283.5438658)
12/23/2023 1:03:10 PM
print cdate( 45283.54387)
12/23/2023 1:03:10 PM

Dec 23, 2023 is 45,283 days AFTER Dec 30, 1899 which is the "0" value. So, 0.5438657407 = Dec 30, 1899 1:03:10 PM

 

[The_Doc_Man]

How can you create criteria in a query to show data at the current time, or at the current hour, or an hour before now, or two hours ago, or to show data during a specific period of time?

I'm going to step back to the original question.

Date and time in Access are intertwined. A DATE variable contains both days and fractions of a day to represent a date and time. The "days" portion is the integer part of a scientific number in format DOUBLE. It is the number of days since the Access "reference" date 31-Dec-1899. Which makes 1-Jan-1900 equal to day 1. Access has an algorithm that takes that day number and turns it into a calendar date using the FORMAT function and one of the pre-defined date format options.

The "time" portion is the fractional part of the DOUBLE number showing a fraction of a day with midnight counting as 0. Thus, Noon is 0.5, 6 PM is 0.75, and so on. Again, since this is a DOUBLE, you have lot of bits to play with... enough in this case to VERY EASILY show times to the second and still have bits left over. The FORMAT function also handles this situation quite easily.

The problem with "showing data at the current time" is that it is a moving target that changes every second. For your question to be meaningful in a practical sense, it must ALWAYS be "for a specific period of time." What is that old saying? "Time and tide wait for no man." (Geoffrey Chaucer.) The current time doesn't stand still in us and in computers.

Showing data for the current hour is easier, since there is a function called HOUR(date-variable) that will extract the hour of the day (00-23) from a date variable. Therefore you could apply an HOUR function to your values to see in which hour they fell. Note that ALL date variables have a time in them even if time wasn't explicitly stored. The time in such cases is 00 (midnight). If you store ONLY times of day in the date variable, then they all have dates of 31-Dec-1899. AND as stated earlier, all times in Access when stored in a DATE variable have an implicit date in them.

Where you would run into trouble is that if you ask for "data from two hours ago" and your night-shift person asks the question at 1 A.M., two hours ago is 11 PM of the previous day. Therefore, you would have issues as noted by Pat Hartman in post #12.

The part of your question regarding "during a specific period of time" is FAR easier. You choose two times, call them Time1 and Time2. Symbolically the solution is therefore to have a WHERE clause in your query that includes ... AND (TIMES BETWEEN TIME1 AND TIME2) ... however, getting TIME1 and TIME2 into the query might be trickier, since it would depend on exactly HOW you determine Time1 and Time2 in the first place. Note that in your original example, your column header was TIME - which for Excel makes no difference, but for Access DOES make a difference, since TIME is a reserved word. You should be able to figure out some of this from the other posts just how to get your times into the query. I don't want to second-guess you on that part of the problem.

 

[ebs17]

Nobody should be surprised if what you wrote down (coded) happens.
Your query:

SELECT
   Table3.Ide,
   Format([Time], "hh:nn AM/PM") AS Times
FROM
   Table3
WHERE
   (((Format([Time], "hh:nn AM/PM")) >= Format(DateAdd("h", - 4, Now()), "hh:nn AM/PM")))

Note: The format statement creates strings. As a result, times are no longer compared here, but texts. Comparisons and sorting of alphanumeric strings follow different rules than for numbers.

Corrected query:

SELECT
   Ide,
   [Time],
   Time() AS ComparisonTime
FROM
   Table3
WHERE
   [Time] >= DateAdd("h", - 4, Time())

 

[shamal]

Thank you all...
Thank you to everyone who participated in helping.
All of your ideas are wonderful, excellent, and fulfill the desired purpose.

 

[Pat Hartman]

@Doc the origin date for Jet/ACE is 12/30/1899, not 12/31/1899

print cdate(1.25)
12/31/1899 6:00:00 AM
print cdate(2.75)
1/1/1900 6:00:00 PM
print cdate(0.75)
6:00:00 PM

Apparently some programmer at MS decided that 12/30/1899 wasn't actually a date so they don't print it

Merry Christmas

 

[The_Doc_Man]

I always confuse that, Pat. Excel uses 1-Jan-1900 as day 1. Access uses it as day 2. Go figure.

 

[CJ_London]

I would also point out that 'Time' is a reserved word and should not be used as a field name

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

Using square brackets usually solves the problem, but sometimes not and you get a weird error