Don't want criteria to filter out null records

[Guest]

I have a table where some fields have entries and some are null. When I
create a query to filter out certain values, it ends up filtering out the
values I want excluded (expected behavior) and it filters out null records
(unexpected and undesired behavior).

For example:
tblSAR with fields SAR_ID, SAR_State, SAR_Title, SAR_Multipurpose
The SAR_Multipurpose field can have null entries.
Example query:
SELECT SAR_ID, SAR_State, SAR_Multipurpose
FROM tblSAR
WHERE SAR_Multipurpose Not Like "*Inc*"

This query not only elminates records with "Inc" in the Multipurpose field,
it also filters out records with a null Multipurpose field. This is not what
I want. I know that I can add another condition (AND SAR_Multipurpose Is
Null).

Is there any other way to get records with a null field to show up in my
query results?

Thank you,
Judy

 

[Guest]

Try adding Or fieldname Is Null

SELECT SAR_ID, SAR_State, SAR_Multipurpose
FROM tblSAR
WHERE SAR_Multipurpose Not Like "*Inc*" Or SAR_Multipurpose Is Null

 

[John Spencer]

Try forcing a value in the SAR_MultiPurpose field by adding a zero-length
string to the field or using the NZ function. My choice would be to use the
zero-length string approach. Since you are using Like and a beginning
wildcard this should not have any impact on performance since any indexes
would not be used on the field

SELECT ...
WHERE SAR_Multipurpose & "" Not Like "*Inc*"