Solved Report Group by Day with a Start time of 4:00:00am, End time 3:59:59AM the next Day (1 Viewer)

kjbrack

Registered User.
Local time
Today, 02:38
Joined
Mar 3, 2015
Messages
20
How can I change the start time of a day to 04:00:00 AM and end on the next day at 03:59:59 AM.

I am getting the records from a query that includes all records from a week. I want to group all records on a single day. For example records from: Friday 2-2-2023 04:00:00 AM till Saturday 2-3-2023 03:59:59 AM would be grouped on Friday 2-2-2023.

My field name is Date_Time and it is in the general date format (01-01-2023 12:25:05 PM).

The report is opened from a command button on a form. Is there a VBA code I would use before opening the report or on the report itself "On Load"?

Thanks in advance for your time. I have spent a couple of hours searching this without any solutions.
 

plog

Banishment Pending
Local time
Today, 01:38
Joined
May 11, 2011
Messages
11,694
Use DateAdd:


Subtract 4 hours from your date value and then obtain the date of the result with DateValue:

 

Gasman

Enthusiastic Amateur
Local time
Today, 07:38
Joined
Sep 21, 2011
Messages
14,607
I would just use a BETWEEN with the math for your time period and Group by Datevalue() of the date?
 

kjbrack

Registered User.
Local time
Today, 02:38
Joined
Mar 3, 2015
Messages
20
Thanks, plog

I added a new column to my query.

Expr1: DateAdd("h",-4,[Date_Time])

In the report I use this field to group by.

Solved, Again many thanks!!!
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:38
Joined
Sep 21, 2011
Messages
14,607
Thanks, plog

I added a new column to my query.

Expr1: DateAdd("h",-4,[Date_Time])

In the report I use this field to group by.

Solved, Again many thanks!!!
That just takes 4 hours off a date/time value? :unsure:
 

kjbrack

Registered User.
Local time
Today, 02:38
Joined
Mar 3, 2015
Messages
20
I was just wanting to group the records on the report only. The record itself will still have it's original date/time in the table. Here is a picture of how it worked on my first week of records. I will not show the Expr1 (Column1) results on the final report, and only show the real date (Column2)

Access_Dates_1.jpg
 
Last edited:

Users who are viewing this thread

Top Bottom