query criteria eliminates too many record results 1

[shopwise]

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?

 

[dhookom]

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

 

[shopwise]

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

 

[dhookom]

If you can't figure this out, please provide the significant fields and their data types as well as the full SQL view of your query.

Duane
Hook'D on Access
MS Access MVP

 

[shopwise]

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

 

[dhookom]

You may have an issue with Nulls. Try:

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

 

[shopwise]

With the added criteria of IsNull, Access gives me the error message of data type mismatch. Apparently IsNull is not used for numeric data?

 

[dhookom]

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

 

[shopwise]

my ignorance!

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

thanks!