Query question

[aoswald]

This is what I have now:

SELECT Contacts.EmployeeName AS Expr1, Nz(Leave.hours,0) AS Expr2, Contacts.LastName, Leave.[Pay Period]
FROM Contacts LEFT JOIN Leave ON Contacts.File_number = Leave.File_Number
WHERE (((Leave.[Pay Period]) Like "11/16*"));

It seems to work until I put in the 11/16 time period and then i don't get the entries with no hours taken

 

[WayneRyan]

aos,

add --> Or Leave.[Pay Period]) Is Null

Also, is [Pay Period] a text field ... but that's another issue.

Wayne

 

[namliam]

Wyan is right...

The way a left join works is that it will allow no value on your "left" side. In this case in Leave.

By putting a constraint on that "no value" side Leave.[Pay Period] Like "11/16*" you reforce it to have a value.... namely 11/16.

You can do this two ways.
1) Or is null
Wyane's solution of adding or is null
WHERE Leave.[Pay Period] Like "11/16*"

2) Constrict the table using a subselect
SELECT Contacts.EmployeeName AS Expr1, Nz(Leave.hours,0) AS Expr2, Contacts.LastName, Leave.[Pay Period]
FROM Contacts LEFT JOIN (Select * from Leave where Leave.[Pay Period] Like "11/16*") as L ON Contacts.File_number = L.File_Number

Good luck and happy coding.