Left join in query

John W. Vinson wrote:
On Thu, 20 May 2010 11:42:01 -0700, Tara
wrote:

FROM QryAmendedContacts, tblDates
WHERE (((QryAmendedContacts.PostedDate) Between
[tblDates]![StartDate] And [tblDates]![EndDate]));

As Daryl and Marshall said, you're using a criterion on the query
field PostedDate. For those records where no query record exists,
that field will be NULL - and NULL is in fact *not* Between those two
dates.

Try changing the WHERE clause to

WHERE QryAmendedContacts.PostedDate Between [tblDates]![StartDate] And
[tblDates]![EndDate] OR QryAmendedContacts.PosteDate IS NULL

Repro:
tblContact:
ContactID PostedDate
1 5/1/2010
2 5/10/2010
3 5/16/2010
4 5/17/2010
5 5/20/2010
6 5/19/2010


tblAmendedDates:
ContactID AmendedDate AmendmentText
1 5/7/2010 ttt
1 5/8/2010 t2
3 5/20/2010 t1
3 5/21/2010 t2
6 5/20/2010 t1


QryAmendedContacts,
SELECT tblContact.ContactID, tblContact.PostedDate,
tblAmendedDates.AmendedDate, tblAmendedDates.AmendmentText
FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;
returns:
ContactID PostedDate AmendedDate AmendmentText
1 5/1/2010 5/7/2010 ttt
1 5/1/2010 5/8/2010 t2
2 5/10/2010
3 5/16/2010 5/20/2010 t1
3 5/16/2010 5/21/2010 t2
4 5/17/2010
5 5/20/2010
6 5/19/2010 5/20/2010 t1

and this query
SELECT QryAmendedContacts.ContactID, QryAmendedContacts.PostedDate,
QryAmendedContacts.AmendedDate, QryAmendedContacts.AmendmentText
FROM QryAmendedContacts
WHERE (((QryAmendedContacts.PostedDate) Between #5/15/2010# And
#5/20/2010#));

returns the expected result:
ContactID PostedDate AmendedDate AmendmentText
3 5/16/2010 5/20/2010 t1
3 5/16/2010 5/21/2010 t2
4 5/17/2010
5 5/20/2010
6 5/19/2010 5/20/2010 t1


What am I missing?


--
Bob Barrows