Why do two queries on same table with same criteria return different dynasets?

[MLH]

Why does this one produce 9 records...
SELECT DISTINCT tblVehicleJobs.VehicleJobID, tblVehicleJobs.Reclaimed,
tblVehicleJobs.VSaleID, tblVehicleJobs.ENF262Written
FROM tblVehicleJobs
WHERE (((tblVehicleJobs.Reclaimed)=No) AND ((tblVehicleJobs.VSaleID)
Is Null) AND ((tblVehicleJobs.ENF262Written)=True));
And this one produces NONE...
SELECT DISTINCT tblVehicleJobs.Reclaimed, tblVehicleJobs.VSaleID,
tblVehicleJobs.ENF262Written
FROM tblVehicleJobs
WHERE (((tblVehicleJobs.Reclaimed)=No) AND ((tblVehicleJobs.VSaleID)
Is Null) AND ((tblVehicleJobs.ENF262Written)=True));
The only difference, AFAIK, is that the first SQL string includes 1
extra output field. I've tested it extensively.

[Allen Browne]

Very odd. You would expect the 2nd query to produce at least one record,
even if all the fields were the same at every record.

Firstly, run a compact/repair in case there is a damaged index.

Next possibility is that you are being bitten by a JET bug that handles
Nulls inconsistently, depending on whether the field is uniquely indexed or
not. We know that VSaleID is Null. It looks like a foreign key, so it
probably is indexed (assuming a relation with referential integrity
enforced), but probably not with a Unique index (unless it is a one-to-one
relation.) In any case, you would expect the bug to produce too many rows,
not too few. This article explains the bug:
DISTINCT query handles Nulls inconsistently
at:
http://allenbrowne.com/bug-12.html

The other two fields present in both queries look like yes/no fields. If
tblVehicleJobs is a local JET table (not an attached table from SQL Server
or some other database), then these fields cannot be null, and even if they
were, the criteria would eliminate the nulls.

If VehicleJobID is the primary key field, and this is a simple little Access
table with just 9 records in it, and the compact/repair does not fix the
problem, I am interested in taking a look at this. Create a new database.
Import the table and the 2 queries. Verify the problem still occurs. Zip it,
and email it to the address in the signature below. (You can't click Reply
as the address is munged to avoid spammers.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.netwrote in message
news:36********************************@4ax.com...

Why does this one produce 9 records...

SELECT DISTINCT tblVehicleJobs.VehicleJobID,
tblVehicleJobs.Reclaimed,
tblVehicleJobs.VSaleID,
tblVehicleJobs.ENF262Written
FROM tblVehicleJobs
WHERE (((tblVehicleJobs.Reclaimed)=No)
AND ((tblVehicleJobs.VSaleID) Is Null)
AND ((tblVehicleJobs.ENF262Written)=True));

>
And this one produces NONE...

SELECT DISTINCT tblVehicleJobs.Reclaimed,
tblVehicleJobs.VSaleID,
tblVehicleJobs.ENF262Written
FROM tblVehicleJobs
WHERE (((tblVehicleJobs.Reclaimed)=No)
AND ((tblVehicleJobs.VSaleID) Is Null)
AND ((tblVehicleJobs.ENF262Written)=True));

>
The only difference, AFAIK, is that the first SQL string includes 1
extra output field. I've tested it extensively.

[MLH]

Nope - sorry 'bout that. I'm just dumber 'n
a bag-o-hammers. I had UniqueRecords
property ON and didn't realize it.

So sorry, but thanks much for the offer of
assistance. I notice that you contribute a
lot to this NG. I'm sure there are a lot of
grateful people out there.