Query returns too many records

[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

 

[Lynn Trapp]

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 <> "") ;

 

[Joan]

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

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 <> "") ;


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

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

 

[Lynn Trapp]

That's great.

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

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

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 <>

"")

;


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

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

 

[Van T. Dinh]

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.

 

[Lynn Trapp]

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

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

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

 

[Lynn Trapp]

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

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

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