Query returns too many records

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

Hi,
I have a query with the following SQL:

SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number], Dogs3Query.[Purchase
Price], Dogs3Query.FinalStore
FROM Dogs3Query
WHERE (((Dogs3Query.FinalStore) Is Not Null));

The problem is that it returns all records even the ones where FinalStore is
Null. Why is it doing this?


Joan
 
Joan,
It might be that the field isn't actually null, but have an empty string or
a number of spaces in it. Either of those cases will still appear to be
null. You might try adding the following to your SQL:

SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number], Dogs3Query.[Purchase
Price], Dogs3Query.FinalStore
FROM Dogs3Query
WHERE (Dogs3Query.FinalStore Is Not Null) OR (Dogs3Query.FinalStore <> "") ;
 
Thanks, Lynn. I tried the SQL you suggested and it works.

Joan


Lynn Trapp said:
Joan,
It might be that the field isn't actually null, but have an empty string or
a number of spaces in it. Either of those cases will still appear to be
null. You might try adding the following to your SQL:

SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number], Dogs3Query.[Purchase
Price], Dogs3Query.FinalStore
FROM Dogs3Query
WHERE (Dogs3Query.FinalStore Is Not Null) OR (Dogs3Query.FinalStore <> "") ;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Joan said:
Hi,
I have a query with the following SQL:

SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number], Dogs3Query.[Purchase
Price], Dogs3Query.FinalStore
FROM Dogs3Query
WHERE (((Dogs3Query.FinalStore) Is Not Null));

The problem is that it returns all records even the ones where
Click to expand...
FinalStore
is
Null. Why is it doing this?


Joan
Click to expand...
Click to expand...
 
That's great.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Joan said:
Thanks, Lynn. I tried the SQL you suggested and it works.

Joan


Lynn Trapp said:
Joan,
It might be that the field isn't actually null, but have an empty string or
a number of spaces in it. Either of those cases will still appear to be
null. You might try adding the following to your SQL:

SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number], Dogs3Query.[Purchase
Price], Dogs3Query.FinalStore
FROM Dogs3Query
WHERE (Dogs3Query.FinalStore Is Not Null) OR (Dogs3Query.FinalStore <>
Click to expand...
"")
;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Joan said:
Hi,
I have a query with the following SQL:

SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number], Dogs3Query.[Purchase
Price], Dogs3Query.FinalStore
FROM Dogs3Query
WHERE (((Dogs3Query.FinalStore) Is Not Null));

The problem is that it returns all records even the ones where
Click to expand...
FinalStore
is
Null. Why is it doing this?


Joan
Click to expand...
Click to expand...
Click to expand...
 
Hi Lynn

My logical brain is unreliable at the moment (as usual) but shouldn't it be
AND rather than OR?

Actually, I shy away from negative of a compound Boolean expression and
would normally use:

....
WHERE Len(Trim([Dog3Query].[FinalStore] & "")) > 0

which should trap both Null and white spaces.
 
Howdy Van,
Actually I think OR is correct in this situation, but I like you solution
much better anyway.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Van T. Dinh said:
Hi Lynn

My logical brain is unreliable at the moment (as usual) but shouldn't it be
AND rather than OR?

Actually, I shy away from negative of a compound Boolean expression and
would normally use:

...
WHERE Len(Trim([Dog3Query].[FinalStore] & "")) > 0

which should trap both Null and white spaces.

--
Cheers
Van


Lynn Trapp said:
Joan,
It might be that the field isn't actually null, but have an empty string or
a number of spaces in it. Either of those cases will still appear to be
null. You might try adding the following to your SQL:

SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number], Dogs3Query.[Purchase
Price], Dogs3Query.FinalStore
FROM Dogs3Query
WHERE (Dogs3Query.FinalStore Is Not Null) OR (Dogs3Query.FinalStore <>
Click to expand...
"")
;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Click to expand...
Click to expand...
 
Hi Van and all other interested readers.

After much reflection and some quick testing, it occurs to me that Van is
100% right. The compound negative Boolean expression threw me for a loop.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Van T. Dinh said:
Hi Lynn

My logical brain is unreliable at the moment (as usual) but shouldn't it be
AND rather than OR?

Actually, I shy away from negative of a compound Boolean expression and
would normally use:

...
WHERE Len(Trim([Dog3Query].[FinalStore] & "")) > 0

which should trap both Null and white spaces.

--
Cheers
Van


Lynn Trapp said:
Joan,
It might be that the field isn't actually null, but have an empty string or
a number of spaces in it. Either of those cases will still appear to be
null. You might try adding the following to your SQL:

SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number], Dogs3Query.[Purchase
Price], Dogs3Query.FinalStore
FROM Dogs3Query
WHERE (Dogs3Query.FinalStore Is Not Null) OR (Dogs3Query.FinalStore <>
Click to expand...
"")
;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Click to expand...
Click to expand...
 
Back
Top