Query Count Doesn't Seem Right

  • Thread starter Thread starter Larry R Harrison Jr
  • Start date Start date
L

Larry R Harrison Jr

I have a query which normally shows 3193 records as it stands. The user
wanted me to screen out those of a certain value.

At first I had it show JUST those with that value; it stated there were 13
of them. So, when I put their criteria in play of showing those without that
value, you'd think there would be 13 less than 3193, right? No! There are 85
less.

I have posted a copy of the database (condensed) at the link above if anyone
is so kind as to show why it's doing this.

The field being screened is LatestRevisionNumber. The value being screened
out is "OBS"

http://www.angelfire.com/az/larrytucaz/tmp/db1.zip
LRH
 
Without looking at your database, have you accounted for Records whose [OBS]
value IS Null?

I think you will find there are 72 Records with [OBS] = Null.

HTH
Van T. Dinh
MVP (Access)
 
i pulled your db (had to convert it to A2K). figured out
that filtering out the records with OBS values (13) also
filters out the records with null values (72). but i can't
for the life of me figure out why, let alone get the nulls
back in.
anybody else?
 
If you have the criteria

WHERE [OBS] <> 13

and if [OBS] = 13 then the expression [OBS] <> 13 will evaluate to Null
since an expression

Null + [Boolean Operator] + anything

will always evaluated to Null, even if the RHS is Null. However, Null is
considered as False in the criteria so the row is not selected. That's why
you need the special case "Is Null" or "Is Not Null".

If you want to include Null, use:

.... WHERE ([OBS] <> 13) OR ([OBS] Is Null)
 
OBS is a criteria value, not a field in larry's query. but
the concept is the point, and when i tried

Not "OBS" Or Is Null

it worked perfectly, delivering 3180 records (original
3193 minus the 13 records with OBS in the filtered field).
i think that is the only criteria combination i DIDN'T try
when i was working on it - duh.

thanks van! :)

-----Original Message-----
If you have the criteria

WHERE [OBS] <> 13

and if [OBS] = 13 then the expression [OBS] <> 13 will evaluate to Null
since an expression

Null + [Boolean Operator] + anything

will always evaluated to Null, even if the RHS is Null. However, Null is
considered as False in the criteria so the row is not selected. That's why
you need the special case "Is Null" or "Is Not Null".

If you want to include Null, use:

.... WHERE ([OBS] <> 13) OR ([OBS] Is Null)

--
HTH
Van T. Dinh
MVP (Access)



i pulled your db (had to convert it to A2K). figured out
that filtering out the records with OBS values (13) also
filters out the records with null values (72). but i can't
for the life of me figure out why, let alone get the nulls
back in.
anybody else?

showing
those without that
Click to expand...


.
Click to expand...
 
Back
Top