CODEWITHSUNDEEP
ms-accessvba
Ali
Ali

Reputation: 613

Filter null, empty values in a datasheet

In an MS Access datasheet, is it possible to filter those records whose value is null or empty in a specific field in my table?

Upvotes: 0

Views: 17919

Answers (3)

David-W-Fenton
David-W-Fenton

Reputation: 23067

Using a function in the criterion means you can't use the indexes. @Mitch Wheat's solution will use the index for the Is Null test, but not for the Len() test.

A sargable WHERE clause would be this:

  WHERE myCol Is Null OR myCol = ""

But I'd recommend turning off "allow zero-length strings" and then you need only do the Null test, which on an indexed field will be extremely fast.

For what it's worth, I find it extremely annoying that MS changed the defaults for new text fields to have Allow ZLS turned on. It means that I have to change every one of them when I'm using the table designer to create new fields.

Upvotes: 1

HK1
HK1

Reputation: 12220

In your SQL statement: 

WHERE Nz(CompanyName, '') = ''

In your form's filter property: 

Nz(CompanyName, '') = ''

The solution posted by Mitch Wheat will also work. I'm not certain which one will offer the best performance. If you are using Mitch's solution and you are also using other conditions in your Where clause, don't forget to insert parenthesis like this: 

WHERE (myCol IS NULL OR LEN(myCol) = 0) AND myCol2 = True

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300669

Yes.

WHERE myCol IS NULL OR LEN(myCol) = 0

Upvotes: 2

Related Questions