Looks like you didn't use a non-equi-join after all... but this change
should give you the records where no delivery date is provided, if I'm
understanding your tables' structures correctly:
SELECT Demographics.DemogID, Demographics.[SS#], Demographics.[Ok To
Contact by Mail], Demographics.[Ok to Contact by Phone], Demographics.
[Home Visit], Demographics.[Home Visitor], Demographics.Address,
Demographics.[Apartment Number], Demographics.City,
Demographics.State, Demographics.[Active Van Client], Demographics.
[ZIP Code], Demographics.[Home Phone Number], Demographics.[Date of
Birth], Demographics.IntakeDate, Demographics.[Van Start Date], [HMB
Test].LMP, DateAdd("d",90,Date()) AS Plus90, DateAdd("d",120,[HMB
Test]!EDC) AS DuePlus90, Demographics.[Van Neighborhood Served],
Demographics.FirstName, Demographics.LastName,
Demographics.MiddleName, [HMB Test].Date, [HMB Test].EDC, [HMB Test].
[Test Results], [Pregnancy Outcomes].[Lost to Followup Date],
Count([HMB Test].TestID) AS CountOfTestID, [Pregnancy Outcomes].
[Baby's DOB], [Pregnancy Outcomes].[Deceased Date]
FROM (Demographics INNER JOIN [HMB Test] ON Demographics.DemogID =
[HMB Test].DemogID) LEFT JOIN [Pregnancy Outcomes] ON
Demographics.DemogID = [Pregnancy Outcomes].DemogID
GROUP BY Demographics.DemogID, Demographics.[SS#], Demographics.[Ok To
Contact by Mail], Demographics.[Ok to Contact by Phone], Demographics.
[Home Visit], Demographics.[Home Visitor], Demographics.Address,
Demographics.[Apartment Number], Demographics.City,
Demographics.State, Demographics.[Active Van Client], Demographics.
[ZIP Code], Demographics.[Home Phone Number], Demographics.[Date of
Birth], Demographics.IntakeDate, Demographics.[Van Start Date], [HMB
Test].LMP, Demographics.[Van Neighborhood Served],
Demographics.FirstName, Demographics.LastName,
Demographics.MiddleName, [HMB Test].Date, [HMB Test].EDC, [HMB Test].
[Test Results], [Pregnancy Outcomes].[Lost to Followup Date],
[Pregnancy Outcomes].[Baby's DOB], [Pregnancy Outcomes].[Deceased
Date]
HAVING (((Demographics.[Active Van Client])=-1) AND (([HMB Test].[Test
Results])="Positive" Or ([HMB Test].[Test Results])="Already
Pregnant") AND (([Pregnancy Outcomes].[Baby's DOB]) Between [HMB Test].
[Date] And [HMB Test].[EDC] OR [Pregnancy Outcomes].[Baby's DOB] Is Null))
ORDER BY Demographics.LastName DESC;
--
Ken Snell
<MS ACCESS MVP>
Jeff said:
SELECT Demographics.DemogID, Demographics.[SS#], Demographics.[Ok To
Contact by Mail], Demographics.[Ok to Contact by Phone], Demographics.
[Home Visit], Demographics.[Home Visitor], Demographics.Address,
Demographics.[Apartment Number], Demographics.City,
Demographics.State, Demographics.[Active Van Client], Demographics.
[ZIP Code], Demographics.[Home Phone Number], Demographics.[Date of
Birth], Demographics.IntakeDate, Demographics.[Van Start Date], [HMB
Test].LMP, DateAdd("d",90,Date()) AS Plus90, DateAdd("d",120,[HMB
Test]!EDC) AS DuePlus90, Demographics.[Van Neighborhood Served],
Demographics.FirstName, Demographics.LastName,
Demographics.MiddleName, [HMB Test].Date, [HMB Test].EDC, [HMB Test].
[Test Results], [Pregnancy Outcomes].[Lost to Followup Date],
Count([HMB Test].TestID) AS CountOfTestID, [Pregnancy Outcomes].
[Baby's DOB], [Pregnancy Outcomes].[Deceased Date]
FROM (Demographics INNER JOIN [HMB Test] ON Demographics.DemogID =
[HMB Test].DemogID) LEFT JOIN [Pregnancy Outcomes] ON
Demographics.DemogID = [Pregnancy Outcomes].DemogID
GROUP BY Demographics.DemogID, Demographics.[SS#], Demographics.[Ok To
Contact by Mail], Demographics.[Ok to Contact by Phone], Demographics.
[Home Visit], Demographics.[Home Visitor], Demographics.Address,
Demographics.[Apartment Number], Demographics.City,
Demographics.State, Demographics.[Active Van Client], Demographics.
[ZIP Code], Demographics.[Home Phone Number], Demographics.[Date of
Birth], Demographics.IntakeDate, Demographics.[Van Start Date], [HMB
Test].LMP, Demographics.[Van Neighborhood Served],
Demographics.FirstName, Demographics.LastName,
Demographics.MiddleName, [HMB Test].Date, [HMB Test].EDC, [HMB Test].
[Test Results], [Pregnancy Outcomes].[Lost to Followup Date],
[Pregnancy Outcomes].[Baby's DOB], [Pregnancy Outcomes].[Deceased
Date]
HAVING (((Demographics.[Active Van Client])=-1) AND (([HMB Test].[Test
Results])="Positive" Or ([HMB Test].[Test Results])="Already
Pregnant") AND (([Pregnancy Outcomes].[Baby's DOB]) Between [HMB Test].
[Date] And [HMB Test].[EDC]))
ORDER BY Demographics.LastName DESC;