Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query criteria eliminates too many record results 1

Status
Not open for further replies.

shopwise

Technical User
Sep 22, 2008
52
US
I have created 2 identical select queries except that one should display records with a value greater than zero and the other should display records less than or equal to zero in the products.productfamily field.

The first query works fine and eliminates all records with a value less than or equal to zero as I have placed >0 in the query criteria field for the products.productfamily field

The trouble is that when I type <=0 in order to eliminate all records that are greater than zero, no record results display even though there are several records that fit the criteria.

This database field data type is numeric and contains a row source lookup value.

what can the issue be?
 
I expect you are attempting to set a criteria against what is displayed rather than what is actually stored. I would get rid of all lookup fields in tables. Then add the table to your query that contains that actual value you want to set your criteria against.

Duane
Hook'D on Access
MS Access MVP
 
thanks for your response.
No, the field is numeric data type and the lookup value is text so when setting criteria with the use of a zero, I am matching the proper data type. The proof is that the criteria does work properly in one of the two queries.
The table that contains that actual value I want to set the criteria against has been added to the query(the products.productfamily field is this actual table field that has already been added).



 
thanks for your responses.

This is the SQL for the query that shows zero out of 268 results:

SELECT images.url_img, images.alt_img, images.productid_img, Products.ProductFamily, Products.ID
FROM images LEFT JOIN Products ON images.productid_img = Products.ID
WHERE (((Products.ProductFamily)<=0));

If you change the <=0 to >0, the correct 140 out of 268 records show up. So if 140 records are greater than zero, shouldn't the remaining 128 records be less than or equal to zero?

Products.ProductFamily data type is numeric, field size is integer, decimal places is auto, default value is 0, required is No, indexed is No
 
You may have an issue with Nulls. Try:
Code:
SELECT images.url_img, images.alt_img, images.productid_img, Products.ProductFamily, Products.ID
FROM images LEFT JOIN Products ON images.productid_img = Products.ID
WHERE Products.ProductFamily<=0 OR Products.ProductFamily Is Null;

Duane
Hook'D on Access
MS Access MVP
 
With the added criteria of IsNull, Access gives me the error message of data type mismatch. Apparently IsNull is not used for numeric data?
 
Shopwise,
I don't see where anyone suggested using IsNull. The "Is Null" criteria can definitely be used with numeric values.

What is the SQL view that you tried?

Duane
Hook'D on Access
MS Access MVP
 
my ignorance!

i tried "Is Null" and this is theeeee solution!

thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top