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 i开发者_C百科n my table?

Yes.

WHERE myCol IS NULL OR LEN(myCol) = 0

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

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.