I'm glad you've got your answer (actually, I'm a little puzzled about
the answer), but I just needed to throw this in:

> SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date
You should stop using "Date" as a field name. It's a reserved keyword
(being the name of a VBA function) and its use as a field name will
cause you problems down the road. If it is not too late in the design
process, you should change that fieldname to something more meaningful
(ContactDate?) so as to avoid the reserved keyword issues.

Tara wrote:
> I can't believe it took three people to make me see this! I guess
> I'd just been at it too long because when I came back in this morning
> and read the comments over again, I realized immediately you were all
> right. I guess my brain was just too overworked. It really needed
> the weekend off.
>


--
HTH,
Bob Barrows

John W. Vinson wrote:
> On Thu, 20 May 2010 11:42:01 -0700, Tara
> <(E-Mail Removed)> 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

John W. Vinson wrote:
> On Thu, 20 May 2010 11:42:01 -0700, Tara
> <(E-Mail Removed)> 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