Check Box fails to return all the required records

[Loggical via AccessMonster.com]

I have query that I am trying to filter using two particular fields; one of
these fields is a Check Box. The query is based on three separate tables
joined by an ID Field on each table.
The problem is when I run the query with the Check Box (Criteria = No) it
doesn’t return all the records it should; only returns two records. I have
checked the underlying tables and it should return some 50+ records. By not
having the Check Box in the same query it returns all the required records,
it should. By setting the (Criteria = Yes) it also returns all the required
records. For some reason it’s just when the (Criteria = No) The query is
updatable.

The SQL for both of the tables are.
Without check box
SELECT tblVFRIssued.VFRIssuedID, tblVFRIssued.VFRIssuedTo, tblVFRIssued.
VFRIssuedDate, tblVFRIssued.VFRIssuedTime, tblVFRIssued.VFRIssuedTitle,
tblVFRIssued.VFRDVDIssued, tblVFRIssued.VFRDVDIssuedSurveillance,
tblVFRIssued.VFRPersonIssued, tblVFRRetrieved.VFRRetrievedID, tblVFRRetrieved.
VFRRetrievedDate, tblVFRRetrieved.VFRRetrievedTime, tblVFRRetrieved.
VFRRetrievedYesNo, tblVFRRetrieved.VFRDVDRetrieved, tblVFRRetrieved.
VFRDVDRetrievedSurv, tblVFRRetrieved.VFRPersonRetrieved, tblVFRDisposed.
VFRDisposedID, tblVFRDisposed.VFRDateDisposed, tblVFRDisposed.VFRTimeDisposed,
tblVFRDisposed.VFRDisposedYesNo, tblVFRDisposed.VFRDVDDisposed,
tblVFRDisposed.VFRDVDSurveillance, tblVFRDisposed.VFRPersonDisposed
FROM (tblVFRIssued LEFT JOIN tblVFRRetrieved ON tblVFRIssued.VFRIssuedID =
tblVFRRetrieved.VFRRetrievedID) LEFT JOIN tblVFRDisposed ON tblVFRRetrieved.
VFRRetrievedID = tblVFRDisposed.VFRDisposedID
WHERE (((tblVFRIssued.VFRIssuedTo)=[Enter the Office]));

With Check Box
SELECT tblVFRIssued.VFRIssuedID, tblVFRIssued.VFRIssuedTo, tblVFRIssued.
VFRIssuedDate, tblVFRIssued.VFRIssuedTime, tblVFRIssued.VFRIssuedTitle,
tblVFRIssued.VFRDVDIssued, tblVFRIssued.VFRDVDIssuedSurveillance,
tblVFRIssued.VFRPersonIssued, tblVFRRetrieved.VFRRetrievedID, tblVFRRetrieved.
VFRRetrievedDate, tblVFRRetrieved.VFRRetrievedTime, tblVFRRetrieved.
VFRRetrievedYesNo, tblVFRRetrieved.VFRDVDRetrieved, tblVFRRetrieved.
VFRDVDRetrievedSurv, tblVFRRetrieved.VFRPersonRetrieved, tblVFRDisposed.
VFRDisposedID, tblVFRDisposed.VFRDateDisposed, tblVFRDisposed.VFRTimeDisposed,
tblVFRDisposed.VFRDisposedYesNo, tblVFRDisposed.VFRDVDDisposed,
tblVFRDisposed.VFRDVDSurveillance, tblVFRDisposed.VFRPersonDisposed
FROM (tblVFRIssued LEFT JOIN tblVFRRetrieved ON tblVFRIssued.VFRIssuedID =
tblVFRRetrieved.VFRRetrievedID) LEFT JOIN tblVFRDisposed ON tblVFRRetrieved.
VFRRetrievedID = tblVFRDisposed.VFRDisposedID
WHERE (((tblVFRIssued.VFRIssuedTo)=[Enter the Office]) AND ((tblVFRRetrieved.
VFRRetrievedYesNo)=No));

Any help would be greatly appreciated.

 

[Allen Browne]

Your query uses an outer join, so it returns all the records from one table,
and if there is no match from the other table, it returns Nulls for those
columns. When you specify criteria on those other columns (whether True or
False), the Nulls don't match, and so the result is as if you had used an
inner join.

More info about joins and criteria in:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have query that I am trying to filter using two particular fields; one of
these fields is a Check Box. The query is based on three separate tables
joined by an ID Field on each table.
The problem is when I run the query with the Check Box (Criteria = No) it
doesn’t return all the records it should; only returns two records. I have
checked the underlying tables and it should return some 50+ records. By
not
having the Check Box in the same query it returns all the required
records,
it should. By setting the (Criteria = Yes) it also returns all the
required
records. For some reason it’s just when the (Criteria = No) The query is
updatable.

The SQL for both of the tables are.
Without check box
SELECT tblVFRIssued.VFRIssuedID, tblVFRIssued.VFRIssuedTo, tblVFRIssued.
VFRIssuedDate, tblVFRIssued.VFRIssuedTime, tblVFRIssued.VFRIssuedTitle,
tblVFRIssued.VFRDVDIssued, tblVFRIssued.VFRDVDIssuedSurveillance,
tblVFRIssued.VFRPersonIssued, tblVFRRetrieved.VFRRetrievedID,
tblVFRRetrieved.
VFRRetrievedDate, tblVFRRetrieved.VFRRetrievedTime, tblVFRRetrieved.
VFRRetrievedYesNo, tblVFRRetrieved.VFRDVDRetrieved, tblVFRRetrieved.
VFRDVDRetrievedSurv, tblVFRRetrieved.VFRPersonRetrieved, tblVFRDisposed.
VFRDisposedID, tblVFRDisposed.VFRDateDisposed,
tblVFRDisposed.VFRTimeDisposed,
tblVFRDisposed.VFRDisposedYesNo, tblVFRDisposed.VFRDVDDisposed,
tblVFRDisposed.VFRDVDSurveillance, tblVFRDisposed.VFRPersonDisposed
FROM (tblVFRIssued LEFT JOIN tblVFRRetrieved ON tblVFRIssued.VFRIssuedID =
tblVFRRetrieved.VFRRetrievedID) LEFT JOIN tblVFRDisposed ON
tblVFRRetrieved.
VFRRetrievedID = tblVFRDisposed.VFRDisposedID
WHERE (((tblVFRIssued.VFRIssuedTo)=[Enter the Office]));

With Check Box
SELECT tblVFRIssued.VFRIssuedID, tblVFRIssued.VFRIssuedTo, tblVFRIssued.
VFRIssuedDate, tblVFRIssued.VFRIssuedTime, tblVFRIssued.VFRIssuedTitle,
tblVFRIssued.VFRDVDIssued, tblVFRIssued.VFRDVDIssuedSurveillance,
tblVFRIssued.VFRPersonIssued, tblVFRRetrieved.VFRRetrievedID,
tblVFRRetrieved.
VFRRetrievedDate, tblVFRRetrieved.VFRRetrievedTime, tblVFRRetrieved.
VFRRetrievedYesNo, tblVFRRetrieved.VFRDVDRetrieved, tblVFRRetrieved.
VFRDVDRetrievedSurv, tblVFRRetrieved.VFRPersonRetrieved, tblVFRDisposed.
VFRDisposedID, tblVFRDisposed.VFRDateDisposed,
tblVFRDisposed.VFRTimeDisposed,
tblVFRDisposed.VFRDisposedYesNo, tblVFRDisposed.VFRDVDDisposed,
tblVFRDisposed.VFRDVDSurveillance, tblVFRDisposed.VFRPersonDisposed
FROM (tblVFRIssued LEFT JOIN tblVFRRetrieved ON tblVFRIssued.VFRIssuedID =
tblVFRRetrieved.VFRRetrievedID) LEFT JOIN tblVFRDisposed ON
tblVFRRetrieved.
VFRRetrievedID = tblVFRDisposed.VFRDisposedID
WHERE (((tblVFRIssued.VFRIssuedTo)=[Enter the Office]) AND
((tblVFRRetrieved.
VFRRetrievedYesNo)=No));

Any help would be greatly appreciated.

 

[Loggical via AccessMonster.com]

So all I had to put was Is Null or Is Not Null.

Many thanks Allen.

Your query uses an outer join, so it returns all the records from one table,
and if there is no match from the other table, it returns Nulls for those
columns. When you specify criteria on those other columns (whether True or
False), the Nulls don't match, and so the result is as if you had used an
inner join.

More info about joins and criteria in:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

I have query that I am trying to filter using two particular fields; one of
these fields is a Check Box. The query is based on three separate tables

[quoted text clipped - 52 lines]

Any help would be greatly appreciated.