Query not returning nulls

[Brent via AccessMonster.com]

Hello, I have a query that will not display all locations from one table
and matched counts from another (with nulls if none). I searched through
the forum for similar answers, but none seem to work.

Any help is appreciated.
(I am running Access 2003)

SELECT Locations.Location, Count(Inspection.Location) AS Inspections
FROM Locations LEFT JOIN Inspection ON Locations.Location =
Inspection.Location
WHERE (((Inspection.Date) Between [start date] And [end date])) OR ((
(Inspection.Date) Is Null))
GROUP BY Locations.Location
ORDER BY Locations.Location;

 

[Pat Hartman]

When you use an outer join (left or right) and your criteria applies to the
many-side table, you need to break the query apart so that the criteria is
applied prior to the join. A simple way is to create two queries:
query1:
SELECT Inspection.Location
FROM Inspection
WHERE Inspection.[Date] Between [start date] And [end date] OR
Inspection.[Date] Is Null;
query2:
SELECT Locations.Location, Count(query1.Location) AS Inspections
FROM Locations LEFT JOIN query1 ON Locations.Location = query1.Location
GROUP BY Locations.Location
ORDER BY Locations.Location;

Alternatively you could use a subselect.

BTW, Date is a poor choice as a column name. It is the name of a function
and will at some point cause you a problem. Never use function or property
names as column names. You should also avoid embedded spaces or special
characters.

 

[Brent via AccessMonster.com]

Thanks for the reply, it worked great. For the "date" field, thanks for the
reminder, this is a query i'm writing for another's database and I didn't
even think about that, so I am changing the field to something more
appropriate.

Thanks again!