picking up null in a query

[Guest]

I'm sure this has been discussed numerous times. I have a query that pulls a
department schedule and shows who worked, the day, the time, and the total
time per shift. It is set right now that an individual will open a form,
select their department from a combo box, and hit a command button that
executes a report that is based off of this query. The problem is it is not
showing any of the shifts where I don't have an employeeID (meaning that the
shift is open). I have tried putting Is Null and Is Not Null in the
criteria, but that still leaves out those that are blank and it makes all the
departments show.

Here is my current SQL:

SELECT tblSchedule.Day, [LastName] & ", " & [FirstName] AS Name,
tblSchedule.Deptartment, tblSchedule.StartTime, tblSchedule.EndTime,
Format([StartTime]-1-[EndTime],"Short Time") AS Total
FROM tblEmployees INNER JOIN tblSchedule ON tblEmployees.EmployeeID =
tblSchedule.EmployeeID
WHERE (((tblSchedule.Deptartment)=[Forms]![SchedulebyDepartment]![Combo0]))
ORDER BY tblSchedule.Day, tblSchedule.Deptartment, tblSchedule.StartTime;


I tried adding the field EmployeeID from tblSchedule and placing the Is Null
Is Not Null in the criteria, but it still leaves out those records with no
EmployeeID

 

[John Spencer (MVP)]

Possibly all you need to do is to change the join from an INNER Join to a RIGHT join

SELECT tblSchedule.Day, [LastName] & ", " & [FirstName] AS Name,
tblSchedule.Deptartment, tblSchedule.StartTime, tblSchedule.EndTime,
Format([StartTime]-1-[EndTime],"Short Time") AS Total
FROM tblEmployees RIGHT JOIN tblSchedule ON tblEmployees.EmployeeID =
tblSchedule.EmployeeID
WHERE (((tblSchedule.Deptartment)=[Forms]![SchedulebyDepartment]![Combo0]))
ORDER BY tblSchedule.Day, tblSchedule.Deptartment, tblSchedule.StartTime;