Access Query Filter Not filter is excluding Blanks

Question

I have a query where I want to exclude results where a certain field has a specific text value. However, I do not want to exclude blanks or other values.

This is what I did: Field A Critera: Not "Healthspan"

When I run this query, it successfully excludes Healthspan, but it also excludes all blank fields. Any solutions for this?

Answer 1

Your query returns only those rows where the condition, [Field A] <> "Healthspan", is True. However, when [Field A] is Null, the condition is evaluated as Null, not True, so that row is excluded.

+--------------+-----------------+
| Field A      | <> "Healthspan" |
+--------------+-----------------+
| "Healthspan" | False           | 
+--------------+-----------------+
| "foo"        | True            |
+--------------+-----------------+
| Null         | Null            |
+--------------+-----------------+

Add a second condition with OR to include those rows with Null [Field A] values:

SELECT y.*
FROM YourTable AS y
WHERE
       y.[Field A] <> "Healthspan"
    OR y.[Field A] Is Null;