DateDiff calculation

[dwaynesworld]

I have a query which will caculate the number of work hours based on weekdays
only.

SELECT tblEmployeeData.Name, Sum(Nz([SumOfHours Taken],0)) AS [Hours Taken],
DateDiff("d",([forms]![ParamForm]![StartDate]),([forms]![ParamForm]![EndDate]))+1 AS [Total Days], qryEmployeeTimeOff.StartDate, qryEmployeeTimeOff.EndDate
FROM tblEmployeeData LEFT JOIN qryEmployeeTimeOff ON tblEmployeeData.Name =
qryEmployeeTimeOff.Instructor
WHERE (((tblEmployeeData.Name) Not Like "*Guest*" And (tblEmployeeData.Name)
Not Like "*Self*"))
GROUP BY tblEmployeeData.Name, qryEmployeeTimeOff.StartDate,
qryEmployeeTimeOff.EndDate;

The problem seems to be that in Jan, it does not calculate the number of
week days correctly. Please help.

 

[KARL DEWEY]

You do not have anything in the query that reflects a requirement for weekdays.

You are saying to Sum(Nz([SumOfHours Taken],0)) as recorded.


You are taking the DateDiff of two entries in your form but they are not
related to the records in any way.

 

[dwaynesworld]

Let me explain what I am trying to do. The employee enters the number of
hours that they have taken off into the database. Whenver I run the query,
what I want to calculate is the total number of available work week hours,
the number of actual hours worked and the number of vacation hours taken by
the employee. In another query, I would then take the available work week
hours and subtract the number of vacation hours to come up with the total
adjusted hours, which I would use to caculate the percentage of hours worked
as a part of available hours. Can you help

You do not have anything in the query that reflects a requirement for weekdays.

You are saying to Sum(Nz([SumOfHours Taken],0)) as recorded.


You are taking the DateDiff of two entries in your form but they are not
related to the records in any way.
--
KARL DEWEY
Build a little - Test a little

I have a query which will caculate the number of work hours based on weekdays
only.

SELECT tblEmployeeData.Name, Sum(Nz([SumOfHours Taken],0)) AS [Hours Taken],
DateDiff("d",([forms]![ParamForm]![StartDate]),([forms]![ParamForm]![EndDate]))+1 AS [Total Days], qryEmployeeTimeOff.StartDate, qryEmployeeTimeOff.EndDate
FROM tblEmployeeData LEFT JOIN qryEmployeeTimeOff ON tblEmployeeData.Name =
qryEmployeeTimeOff.Instructor
WHERE (((tblEmployeeData.Name) Not Like "*Guest*" And (tblEmployeeData.Name)
Not Like "*Self*"))
GROUP BY tblEmployeeData.Name, qryEmployeeTimeOff.StartDate,
qryEmployeeTimeOff.EndDate;

The problem seems to be that in Jan, it does not calculate the number of
week days correctly. Please help.

 

[KARL DEWEY]

Post the table structure, field names and datatype, where the number of
actual hours worked and the number of vacation hours taken by the employee
are recorded.
Post sample data.
Post what you would want the results to look like.
--
KARL DEWEY
Build a little - Test a little

Let me explain what I am trying to do. The employee enters the number of
hours that they have taken off into the database. Whenver I run the query,
what I want to calculate is the total number of available work week hours,
the number of actual hours worked and the number of vacation hours taken by
the employee. In another query, I would then take the available work week
hours and subtract the number of vacation hours to come up with the total
adjusted hours, which I would use to caculate the percentage of hours worked
as a part of available hours. Can you help

You do not have anything in the query that reflects a requirement for weekdays.

You are saying to Sum(Nz([SumOfHours Taken],0)) as recorded.


You are taking the DateDiff of two entries in your form but they are not
related to the records in any way.
--
KARL DEWEY
Build a little - Test a little

I have a query which will caculate the number of work hours based on weekdays
only.

SELECT tblEmployeeData.Name, Sum(Nz([SumOfHours Taken],0)) AS [Hours Taken],
DateDiff("d",([forms]![ParamForm]![StartDate]),([forms]![ParamForm]![EndDate]))+1 AS [Total Days], qryEmployeeTimeOff.StartDate, qryEmployeeTimeOff.EndDate
FROM tblEmployeeData LEFT JOIN qryEmployeeTimeOff ON tblEmployeeData.Name =
qryEmployeeTimeOff.Instructor
WHERE (((tblEmployeeData.Name) Not Like "*Guest*" And (tblEmployeeData.Name)
Not Like "*Self*"))
GROUP BY tblEmployeeData.Name, qryEmployeeTimeOff.StartDate,
qryEmployeeTimeOff.EndDate;

The problem seems to be that in Jan, it does not calculate the number of
week days correctly. Please help.