NULL isn't always NULL

[JohnB]

When I run the query below I sometimes get fields that are NOT blank. What
causes that? What am I doing wrong?
I only want records returned that meet the first criteria and where the
ProductImage1 field is blank.

SELECT Products.ProductName, Products.ProductImage1, Products.ProductStock
FROM Products
WHERE (((Products.ProductName)="ocala")) OR (((Products.ProductName)="ocala,
fl") AND ((Products.ProductImage1) Is Null));


TIA

 

[Allen Browne]

After removing spurious brackets, your WHERE clause is:
WHERE Products.ProductName="ocala"
OR (Products.ProductName="ocala, fl"
AND Products.ProductImage1 Is Null)

There are 2 ways to satisify the condition:
a) if the product name is 'ocala', that's enough;
b) if the product name is 'ocald, fl', and the image is blank, that's enough
too.

Perhaps you intended:
WHERE (Products.ProductName="ocala"
OR Products.ProductName="ocala, fl")
AND Products.ProductImage1 Is Null

In this case, 2 conditions must both be met: one of the product names, and
the image must be blank.

 

[Phil]

When I run the query below I sometimes get fields that are NOT blank.

What causes that? What am I doing wrong?

You are including too many unnecessary parentheses, making it difficult to
see that you have one or more of them in the wrong place.

I only want records returned that meet the first criteria and where the
ProductImage1 field is blank.

I assume you want to return records that match either of the first two
criteria? otherwise why have you included the OR condition?

SELECT Products.ProductName, Products.ProductImage1, Products.ProductStock
FROM Products
WHERE (((Products.ProductName)="ocala")) OR
(((Products.ProductName)="ocala, fl") AND ((Products.ProductImage1) Is
Null));

Try this:

WHERE (Products.ProductName="ocala" OR Products.ProductName="ocala,
fl") AND Products.ProductImage1 Is Null;

 

[JohnB]

Your 2nd scenario was what I was trying to achieve.
I had originally created the query with only the OR's, then after adding the
AND Access added the extra brackets, and I didn't notice that.

Thanks!