Calculation troubles

[CraigBFG]

I'm trying to calculate the number of holidays a person is entitled to based on their leaving date.

For every 18.25 days, the employee is entitled to 1 holiday, then deduct those already taken and your left with the balance of entitlement.

Eg
StartDate : 01/01/2004
LeavingDate : 01/10/2004
Days between dates : 274
Holiday calculcation : 18.25
Days holiday already taken : 7
***** Calculated days = 8

This all works fine and calculates perfectly - UNTIL for example a person hasn't taken any days off (holidays taken = null/zero). Then the query doesn't show anything, where in this example it should show calculated days as 15.

My query is below - any ideas guys/gals would be most appreciated.


SELECT [Emp_FirstName] & " " & [Emp_Surname] AS EmpName, DateDiff('d',[StartDate],[EndDate]) AS DaysBetween, [DaysCalc] AS DaysCalcs, round(DateDiff('d',[StartDate],[EndDate])/[DaysCalc]-Sum([Absence_Days]),1) AS DaysLeft, t_Employees.Emp_Id, t_Absence.Absence_DateFrom, Sum(nz([Absence_Days])) AS AbsenceDays
FROM t_Absence_Reasons INNER JOIN (t_Employees RIGHT JOIN t_Absence ON t_Employees.Emp_Id = t_Absence.Emp_Id) ON t_Absence_Reasons.Ab_Reason_Id = t_Absence.Ab_Reason_Id
WHERE (((t_Absence_Reasons.Ab_Reason_Desc)="holiday"))
GROUP BY [Emp_FirstName] & " " & [Emp_Surname], DateDiff('d',[StartDate],[EndDate]), [DaysCalc], t_Employees.Emp_Id, t_Absence.Absence_DateFrom
HAVING (((t_Employees.Emp_Id)=[forms]![f_Employee_Leave_Select]![Emp_Id]) AND ((t_Absence.Absence_DateFrom) Between [StartDate] And [EndDate]));

Thanks
Craig

 

[rockman]

Nulls cause calculations to return Null.

Type these examples into the immediate window:

? 3+4+Null
Null


? 3+4+Nz(Null)
7


Therefore, surround your fields with Nz().