CODEWITHSUNDEEP
sqlms-access
squeegene
squeegene

Reputation: 487

Like * does not capture Is Null values

I have an Access Query that filters a table in my database using 

Like "*" & [Forms]![Reports]![Filter] & "*"

which grabs filtered items if that filter is selected, or returns everything if that filter is unselected. However, it misses Null values. If the filter is unselected, the string becomes

Like **

which does not capture Null values.

How can I capture these values in the way I describe?

Upvotes: 0

Views: 1019

Answers (3)

Jim Horn
Jim Horn

Reputation: 889

The above two answers are correct, but to provide some added context NULL cannot be compared to anything. It's not 'not 5', 'not like "**", 'not like "foo"', not 'Date < #12/31/0000 BC#', not False, you get the idea. The only way to pick it off is to do as the above experts state, IS NULL or IS NOT NULL.

Upvotes: 2

Ross Bush
Ross Bush

Reputation: 15175

You can do this by or'ing the null check so it returns data for both conditions -->

(Like "*" & [Forms]![Reports]![Filter] & "*" OR MyField IS NULL)

Also, you should be able to check the UI is absent a value-->

 (Like "*" & [Forms]![Reports]![Filter] & "*" OR [Forms]![Reports]![Filter] = "" )

Upvotes: 2

ashleedawg
ashleedawg

Reputation: 21619

In Microsoft Access Query criteria, you can use an OR as well:

Like "*" & [Forms]![Reports]![Filter] & "*" Or Is Null

...to have Null not return, then use:

Is Not Null

...or,

Like "*" & [Forms]![Reports]![Filter] & "*" And Is Not Null

Upvotes: 1

Related Questions