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.
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.
Last edited: