Date and Time Range Problems for a Report (1 Viewer)

[Kreuz]

I have operators that enter equipment downtime information into the database. I need to create a report that pulls all records that have a start date and time based on a start date (field name FromDate on form) at 7:00 AM to an end date (field name ToDate on form) at 6:59 AM and shows the total duration in hours for each record.

I'm having difficulty getting my query to display the correct results for the date and time range. Duration calculations work perfectly.

Here is my main table only using fields pertinent to this post:

Field Name Data Type Description
Exception_Number AutoNumber Primary Key
ExceptStartDate Date/Time Start Date formatted as Short Date
ExceptStartTime Date/Time Start Time formatted as Medium Time
ExceptEndDate Date/Time End Date formatted as Short Date
ExceptEndTime Date/Time End Time formatted as Medium Time

Here's the record set for this post:

Exception Start Start End End
Number Date Time Date Time
1 8/6/2013 4:00 AM 8/6/2013 4:20 AM
2 8/6/2013 9:00 AM 8/6/2013 10:00 AM
3 8/6/2013 2:00 PM 8/6/2013 3:30 PM
4 8/7/2013 6:00 AM 8/7/2013 6:30 AM
5 8/7/2013 7:00 AM 8/7/2013 7:00 PM
6 8/7/2013 7:30 AM 8/7/2013 8:00 AM
7 8/7/2013 10:00 AM 8/7/2013 10:30 AM

The query that drives the report includes all fields mentioned above plus these calculated fields:

StartDateTime: [ExceptStartDate] & " " & [ExceptStartTime]
EndDateTime: [ExceptEndDate] & " " & [ExceptEndTime]
Duration: Round(DateDiff("n",[StartDateTime],[EndDateTime])/60,1)

Base SQL Statement:
SELECT tblException_Report_Details.Exception_Number, tblException_Report_Details.ExceptStartDate, tblException_Report_Details.ExceptStartTime, tblException_Report_Details.ExceptEndDate, tblException_Report_Details.ExceptEndTime, [ExceptStartDate] & " " & [ExceptStartTime] AS StartDateTime, [ExceptEndDate] & " " & [ExceptEndTime] AS EndDateTime, Round(DateDiff("n",[StartDateTime],[EndDateTime])/60,1) AS Duration
FROM tblException_Report_Details;

For a given FromDate of 8/6/2013 and ToDate of 8/7/2013 the query should show records 2,3,and 4 as the results.

I tried: Between ([Forms]![frmReports_Menu]![FromDate]+7/24) And ([Forms]![frmReports_Menu]![ToDate]+7/24) as criteria for StartDateTime and got records 2,4,5 and 7.

SQL Statement:
SELECT tblException_Report_Details.Exception_Number, tblException_Report_Details.ExceptStartDate, tblException_Report_Details.ExceptStartTime, tblException_Report_Details.ExceptEndDate, tblException_Report_Details.ExceptEndTime, [ExceptStartDate] & " " & [ExceptStartTime] AS StartDateTime, [ExceptEndDate] & " " & [ExceptEndTime] AS EndDateTime, Round(DateDiff("n",[StartDateTime],[EndDateTime])/60,1) AS Duration
FROM tblException_Report_Details
WHERE ((([ExceptStartDate] & " " & [ExceptStartTime]) Between ([Forms]![frmReports_Menu]![FromDate]+7/24) And ([Forms]![frmReports_Menu]![ToDate]+7/24)));

I tried:
>=DateAdd("h",7,[Forms]![frmReports_Menu]![FromDate]) And <DateAdd("h",7,[Forms]![frmReports_Menu]![ToDate]) as criteria for StartDateTime and got records 2,4, and 7.

SQL Statement:
SELECT tblException_Report_Details.Exception_Number, tblException_Report_Details.ExceptStartDate, tblException_Report_Details.ExceptStartTime, tblException_Report_Details.ExceptEndDate, tblException_Report_Details.ExceptEndTime, [ExceptStartDate] & " " & [ExceptStartTime] AS StartDateTime, [ExceptEndDate] & " " & [ExceptEndTime] AS EndDateTime, Round(DateDiff("n",[StartDateTime],[EndDateTime])/60,1) AS Duration
FROM tblException_Report_Details
WHERE ((([ExceptStartDate] & " " & [ExceptStartTime])>=DateAdd("h",7,[Forms]![frmReports_Menu]![FromDate]) And ([ExceptStartDate] & " " & [ExceptStartTime])<DateAdd("h",7,[Forms]![frmReports_Menu]![ToDate])));

Other attempts have resulted in inaccuracies as well.

So, what is the best way for me to accomplish this?

My apologies if I put too much info on this post.

 

[spikepl]

This is what happens when you mess up data types. You have forced Access to pereform a string comparison, which it does to perfection.

Your expressions produced strings. All your Date or Time fields in the query must be of type Date/Time. If you operate on date/time you'll get the right results.

StartDateTime: [ExceptStartDate] + [ExceptStartTime]
EndDateTime: [ExceptEndDate] + [ExceptEndTime]

 

[Kreuz]

Thanks Spike. I had a feeling it had something to do with that. Works perfectly.

 

[Cronk]

You of course realise however that the query using only the Start Date/Time will give the down time only for events starting in the period, not for down time in the period ie events which started before the start time and carry on into the selected period.