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
|