Convert Null to Zero in query

[Bob Groger]

Hello,

I am trying to run a 2 week payroll report from timesheets entered in a
table. I have a query working that returns all employees and the hours
worked each week, but if they did not work 1 of the weeks the
SumOf_Total_Paid_Time for the week is Null. I need to change the null to 0.
I have experimented with the nz() function but have not been successful yet.
My questions are:
1. Is nz() the right approach here?
2. If so how and where would it be implemented in the following queries?

Thanks!
Bob G

TestWk1: SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First
Name], Sum(Timesheet.Total_Paid_Time) AS SumOfTotal_Paid_Time
FROM Employees INNER JOIN Timesheet ON Employees.Empl_ID=Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between forms.dateentry.wk1start And
forms.dateentry.wk1end))
GROUP BY Employees.Empl_ID, Employees.[Last Name], Employees.[First Name];

TestWk2: SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First
Name], Sum(Timesheet.Total_Paid_Time) AS SumOfTotal_Paid_Time
FROM Employees INNER JOIN Timesheet ON Employees.Empl_ID =
Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between [forms].[dateentry].[wk2start] And
[forms].[dateentry].[wk2end]))
GROUP BY Employees.Empl_ID, Employees.[Last Name], Employees.[First Name];

TestTotal: SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First
Name], TestWk1.SumOfTotal_Paid_Time, TestWk2.SumOfTotal_Paid_Time
FROM (Employees LEFT JOIN TestWk1 ON Employees.Empl_ID = TestWk1.Empl_ID)
LEFT JOIN TestWk2 ON Employees.Empl_ID = TestWk2.Empl_ID;

 

[fredg]

Hello,

I am trying to run a 2 week payroll report from timesheets entered in a
table. I have a query working that returns all employees and the hours
worked each week, but if they did not work 1 of the weeks the
SumOf_Total_Paid_Time for the week is Null. I need to change the null to 0.
I have experimented with the nz() function but have not been successful yet.
My questions are:
1. Is nz() the right approach here?
2. If so how and where would it be implemented in the following queries?

Thanks!
Bob G

TestWk1: SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First
Name], Sum(Timesheet.Total_Paid_Time) AS SumOfTotal_Paid_Time
FROM Employees INNER JOIN Timesheet ON Employees.Empl_ID=Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between forms.dateentry.wk1start And
forms.dateentry.wk1end))
GROUP BY Employees.Empl_ID, Employees.[Last Name], Employees.[First Name];

TestWk2: SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First
Name], Sum(Timesheet.Total_Paid_Time) AS SumOfTotal_Paid_Time
FROM Employees INNER JOIN Timesheet ON Employees.Empl_ID =
Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between [forms].[dateentry].[wk2start] And
[forms].[dateentry].[wk2end]))
GROUP BY Employees.Empl_ID, Employees.[Last Name], Employees.[First Name];

TestTotal: SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First
Name], TestWk1.SumOfTotal_Paid_Time, TestWk2.SumOfTotal_Paid_Time
FROM (Employees LEFT JOIN TestWk1 ON Employees.Empl_ID = TestWk1.Empl_ID)
LEFT JOIN TestWk2 ON Employees.Empl_ID = TestWk2.Empl_ID;

Sum(Nz(Timesheet.Total_Paid_Time,0) AS SumOfTotal_Paid_Time