Date and Time Criteria

  • Thread starter Thread starter Jerrett
  • Start date Start date
J

Jerrett

The data in my Date/Time field looks like this
"04/05/2003 03:30:00 AM" I would like to SUM adjacent
data (next field) between 8:00:01 AM on day1 and 8:00:00
AM the next day. I need 8 to 8 data for each day. I
would like to do this using CRITERIA instead of writing
code.
 
SELECT Sum(Table1.SumMe) AS SumOfSumMe
FROM Table1
HAVING (((Table1.DateTime)<=Date()+#8:00:00# And (Table1.DateTime)>=Date()-1+#8:00:01#));
 
Let me restate the problem as I understand it.

You want to sum a set of records grouped by day where the day runs from 8AM to 8AM.

I would try using SQL that looked like the following UNTESTED SQL Statement:

Parameters StartDate DateTime, EndDate DateTime;
SELECT DateAdd("h",-8,DateField) as AdjustedDate,
SUM(NextField)
FROM YourTableName
WHERE DateField >= DateAdd("h",8,[StartDate]) AND
DateField < DateAdd("h",8,[EndDate])
GROUP BY DateAdd("h",-8,DateField)

You may have to play with the DateAdd functions to get them to give you the
correct results.
 
Back
Top