Solved Results within a 24hr period (1 Viewer)

dawsonrhodes

Member
Local time
Yesterday, 22:39
Joined
Mar 8, 2020
Messages
85
Hey All!

I have a query that I am trying to run that'll populate a chart showing transactions from one period to another within the "day".

Essentially, in our business we have a time period which transactions "reset" a threshold. At 4AM every day I need to clear the query and begin only showing results from, for example, 4AM May 25, 2021 to 3:59AM May 26, 2021.

Could a between criteria be used to basically say that Date() between 4AM and 11:59 and Date() +1 between 12AM and 3:59PM?

The name of the field/control in this case is [RoFDateCompleted]

Any ideas?

Cheers!
 
Last edited:

dawsonrhodes

Member
Local time
Yesterday, 22:39
Joined
Mar 8, 2020
Messages
85
Date() 04:00am# and Date()+1 03:59am (the basic needs)

this is what I've come up with so far

Between #Date() 04:0:0# And #Date() +1 03:59:0#
 
Last edited:

plog

Banishment Pending
Local time
Yesterday, 21:39
Joined
May 11, 2011
Messages
11,613
Ok? And how did it turn out?
 

plog

Banishment Pending
Local time
Yesterday, 21:39
Joined
May 11, 2011
Messages
11,613
Knowing where to put the # is tricky--especially when you want to use a function. Here'es the code I would use for the criteria:

>CDate(Date() & " 3:59:59 AM") And <=CDate((Date()+1) & " 3:59:59 AM")

CDate returns a date, so no need for quote marks nor hashes.
 

dawsonrhodes

Member
Local time
Yesterday, 22:39
Joined
Mar 8, 2020
Messages
85
Between #2/5/2006 14:7:0# And #11/18/2006 17:28:15#

I found this example online, but I want it to be Date() and Date() +1 with 4AM and 3:59AM

This code works, but doesn't display the record I'd want
 

dawsonrhodes

Member
Local time
Yesterday, 22:39
Joined
Mar 8, 2020
Messages
85
Knowing where to put the # is tricky--especially when you want to use a function. Here'es the code I would use for the criteria:

>CDate(Date() & " 3:59:59 AM") And <=CDate((Date()+1) & " 3:59:59 AM")

CDate returns a date, so no need for quote marks nor hashes.
Thank you, I'll try this and report back!
 

dawsonrhodes

Member
Local time
Yesterday, 22:39
Joined
Mar 8, 2020
Messages
85
Knowing where to put the # is tricky--especially when you want to use a function. Here'es the code I would use for the criteria:

>CDate(Date() & " 3:59:59 AM") And <=CDate((Date()+1) & " 3:59:59 AM")

CDate returns a date, so no need for quote marks nor hashes.
Hey plog it appears you're a genius and I thank you very much! I'll mark as solved and hope someone down the line finds this to help them too!
 

Users who are viewing this thread

Top Bottom