How do i use "not equal to" in a ms query?

[WickidWe]

I am trying to use the following simple query to display all employee names that are not in the leave table
on the selected date from a form and it is coming up blank.

SELECT tblEmpDetails.Name


FROM (tblEmpDetails LEFT JOIN tblEmpLeave ON tblEmpDetails.EmpId = tblEmpLeave.EmpID) LEFT JOIN tbl36hrWeekRosters ON tblEmpLeave.Date_ = tbl36hrWeekRosters.Date_
WHERE (((tblEmpDetails.EmpId)<>[tblEmpLeave].[EmpID]) AND ((tbl36hrWeekRosters.Date_)=[Forms]![Form1]![txtDate]));if I change the <> for = it shows only the employees in the leave table.

How do i use "not equal to" in a ms query?

[June7]

Try:

WHERE [tblEmpLeave].[EmpID] Is Null AND

[WickidWe]

When I try this in design view it changes the sql to ="Is Null" and if i put is straight in the SQL it comes back with a syntax error "Missing Operator"

[June7]

I don't know why that would happen. Is Null is perfectly legitimate criteria.

[Dal Jeanis]

I don't understand what the 36hrWeekRoster table is. It only seems to have relevant dates on it, so I'm going to ignore my confusion. If there is any employee info linked into it, then your join is wrong. If not, then there is no reason to include it in the JOIN, since it must match the date on the tblEmplLeave table and the date in the form anyway.

So, in English, what are you trying to do?

"I want to see all employees who are not in the tblEmpLeave table for the week in the txtDate field."

SELECT 
   TE.Name
FROM 
   tblEmpDetails AS TE
WHERE 
   TE.EmpId Not IN 
   ( SELECT TL.EmpID
     FROM tblEmpLeave AS TL
     WHERE TE.Date_ =[Forms]![Form1]![txtDate]);

This would assume that you have elsewhere constrained the txtDate to be selected from dates in tbl36hrWeekRoster.

[WickidWe]

Thanks, I finally got it . I just had to change the Where TE.Date_ to TL.Date as only the leave table has a date associated with it.

[Dal Jeanis]

Glad you got it worked out.