Basic Query Question 1

[ptrifile]

I have what appears to be something simple that i cannot seem to figure out.

I have 2 tables, TblEmployeeMaster and TblTimeOff. They are joined by EmployeeNumber

I am trying to create a query that will only show me employees that do not have an entry in TblTimeOff for a date that is on a form.

The SQL is:

SELECT tblEmployeeMaster.EmployeeNumber, tblEmployeeMaster.EmployeeFirstName, tblEmployeeMaster.EmployeeLastName, TblTimeOff.timeoffdate
FROM tblEmployeeMaster LEFT JOIN TblTimeOff ON tblEmployeeMaster.EmployeeNumber = TblTimeOff.EmployeeNumber
WHERE (((TblTimeOff.timeoffdate)<>[forms]![frmshiftconfiguration]![txtstartdate]));

I hope this makes some sort of sense to someone. Any help/suggestions welcomed.

Thank you in advance!

Paul

 

[dhookom]

Try something like:

SELECT tblEmployeeMaster.EmployeeNumber, tblEmployeeMaster.EmployeeFirstName, 
tblEmployeeMaster.EmployeeLastName, TblTimeOff.timeoffdate
FROM tblEmployeeMaster LEFT JOIN TblTimeOff ON 
tblEmployeeMaster.EmployeeNumber = TblTimeOff.EmployeeNumber
WHERE Nz(TblTimeOff.timeoffdate,#01/01/1900#)<>[forms]![frmshiftconfiguration]![txtstartdate];

Duane
Hook'D on Access
MS Access MVP

 

[ptrifile]

Works perfectly! Thank you very much dhookom!

 

[ptrifile]

Not sure if i should start a new thread or not as what dhookom suggested worked perfectly but I am finding a need to change this a little bit. How could I modify the solution above to show all records except ones that match [forms]![frmshiftconfiguration]![txtstartdate]

Thank you in advance.

Paul

 

[ptrifile]

Please disregard question.