query / group by date that isn't from 00:00 to 00:00 1

[rxsims]

How do I query and group data for a period of time greater than one day when the I want the 'days' to be grouped from another time, i.e. 05:00 to 05:00 every day instead of 00:00 to 00:00?

 

[Norris68]

Specify the time too. e.g.

SELECT .... WHERE MyDate BETWEEN #1/1/80 05:00:00# AND #1/2/80 05:00:00#

Either that, or add/delete 5 hours to all of your dates within your query to 'shift' the working day.

 

[rxsims]

Thanks. When I add the times to the Select.. statement, I think that will only group for the first day and last day, but all the days in between will be from 00:00 to 00:00. I'll try shifting the times. I will have to shift the time backward 5 hours in a query and shift them forward again in the report.
Thanks again.

 

[Norris68]

You don't need to shift all of your dates - you could just do it within the WHERE clause. e.g.

SELECT MyDate FROM MyTable WHERE DateAdd("h",5,MyDate) BETWEEN #01/01/80 05:00:00# AND #01/02/80 05:00:00#

Then you will not need to shift them back in your form/report.