ok here is what I have so far
Code:
SELECT [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned], DateDiff("h",[Date & Time Left],[Date Returned]) AS TotalHours
FROM [Message Tracking]
GROUP BY [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned]
HAVING ((([Message Tracking].[Date & Time Left]) Between [Begin_Date & Time Left] And [End_Date Returned]));
and this is the result I got using a date range of 10/20/2014 6:00 AM TO 10/21/2014 (the [Date Returned] field is a Date/Time field which doesn't have the time in all the fields only the Dates so it isn't consistent like the [Date & Time Field]
Date & Time Left |
Date Returned |
TotalHours |
10/20/14 6:32 AM |
10/21/2014 |
18 |
10/20/14 6:35 AM |
10/21/2014 |
18 |
10/20/14 6:38 AM |
10/21/2014 |
18 |
10/20/14 7:00 AM |
10/21/2014 |
17 |
10/20/14 7:06 AM |
10/21/2014 |
17 |
10/20/14 7:07 AM |
10/21/2014 |
17 |
10/20/14 7:10 AM |
10/21/2014 |
17 |
10/20/14 7:14 AM |
10/21/2014 |
17 |
10/20/14 7:19 AM |
10/21/2014 |
17 |
10/20/14 7:20 AM |
10/21/2014 |
17 |
10/20/14 7:21 AM |
10/20/2014 2:00:00 PM |
7 |
10/20/14 7:21 AM |
10/21/2014 |
17 |