Left join in query
John W. Vinson wrote:
On Thu, 20 May 2010 11:42:01 -0700, Tara
wrote:
Here is the query (that doesn't work) that I'm trying to use the
above query in:
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
I don't understand. PostedDate comes from tblContact, the left side of
the query:
..., tblContact.PostedDate, ...
FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID =
tblAmendedDates.ContactID;
If it's not Null in the original table, why would it be Null in the
output of QryAmendedContacts?
--
HTH,
Bob Barrows
|