Making 8-5 Mon-Friday = 5 days

[Eric]

Hi everyone,

In my report, I am subtracting my end dates from my start
date to get the specified number of days it takes for a
person to get done with a project. Our hours are Monday -
Friday, 8am to 5pm. This should equal 5 days. I DO NOT
want Saturday and Sunday calculated in my report. Can I do
this in a query or is it easier to do this in the report
module?

Here is my query currently:

SELECT Plant.Name, User.Name, AnalysisTestGroup.StartDate,
AnalysisTestGroup.EndDate, AnalysisTestGroup.ID,
qryAnalysisCount.Sets
FROM qryAnalysisCount INNER JOIN (User INNER JOIN (Plant
RIGHT JOIN AnalysisTestGroup ON Plant.Code =
AnalysisTestGroup.PlantCode) ON User.ID =
AnalysisTestGroup.TestPerson) ON
qryAnalysisCount.TestGroupID = AnalysisTestGroup.ID
WHERE (((AnalysisTestGroup.EndDate) Is Not Null))
GROUP BY Plant.Name, User.Name,
AnalysisTestGroup.StartDate, AnalysisTestGroup.EndDate,
AnalysisTestGroup.ID, qryAnalysisCount.Sets
HAVING (((AnalysisTestGroup.StartDate) Between [Forms]!
[frmReports]![txtStartDate] And [Forms]![frmReports]!
[txtEndDate]))
ORDER BY AnalysisTestGroup.StartDate;

 

[Douglas J. Steele]

Take a look at the Date/Time section of "The Access Web"
http://www.mvps.org/access/

There are a couple of solutions there