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

[kjbrack]

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]

Use DateAdd:

MS Access: DateAdd Function

This MSAccess tutorial explains how to use the Access DateAdd function with syntax and examples. The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

www.techonthenet.com

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

MS Access: DateValue Function

This MSAccess tutorial explains how to use the Access DateValue function with syntax and examples. The Microsoft Access DateValue function converts a string to a date.

www.techonthenet.com

 

[Gasman]

I would just use a BETWEEN with the math for your time period and Group by Datevalue() of the date?

 

[kjbrack]

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]

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?

 

[kjbrack]

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)