How to Filter for a Null Value?

[NoSmoke]

Can't figure out the syntax for specifying a null value for a text or combo box when filtering a table via a form. "Is Null" seems to work for query criteria but doesn't work for form controls. If one right-clicks on a form control displaying a null value, one of the choices is "Equals Blank" which if selected works fine but what does one use for the text filter if the record for the control in question does is not displaying a null value?? TIA for any insight.....

PS, above was also posted on MrExcel but no replies resulted.

 

[Pat Hartman]

"Is Null" is SQL syntax. In VBA, you need to use the IsNull() function. You also have to be aware that the "blank" control might actually be a ZLS(zero-length-string). There are two simple methods for testing for both values at once.

If Len(Me.somefield) = 0 Then

or

If Me.somefield & "" = "" Then

The first test checks the length of a value. It will be 0 if the control is null or a zls. The second concatenates a zls to the value and the result will return a zls if the control contains a null or zls.

The "Equals Blank" choice is really misleading but in this context it seems to mean nulls or zero-length-strings. Since I write queries, I don't use the built in filters much. They are too inefficient to work with large tables.

 

[NoSmoke]

Thanks for that information Pat but I would still like to know how to specify a null field for a filter if anyone can help with that. This would be for occasional use so efficiency is not that important.

 

[Pat Hartman]

IsNull(Somefield) = True
or
IsNull(Somefield) = False

 

[Galaxiom]

BTW Note that the filter is applied to the form's recordset so it must refer to fields there rather than controls (textboxes etc) on the form.

The expression must return a value that can be interpreted as true or false so the equal test is redundant when the function returns a Boolean. The following two expressions are effectively the same as those Pat posted.

IsNull(Somefield)

Not IsNull(SomeField)

 

[NoSmoke]

Thanks guys for the help but I fear I am not explaining the situation properly (or I am misunderstanding your replies).

If one right-clicks on a text or combo box on a form, a pop-up menu appears that gives choices for sorting or filtering on the text or combo contents. One of those options is "Text Filters". Hovering over that results in another pop-up containing options for the test to be performed on the text or combo contents such as "Equals...", "Does Not Equal..." etc. When one clicks on one of those choices, a text entry box appears and what I am trying to figure out is what text one would enter to specify a null value i.e. I want to filter out each record that contains (or equals) a null value in the field for that specific text or combo box.

IOWs I want to determine which records do not yet have an entry for that particular text or combo box and to do that not with a query but from within the form itself.

Update:

By trial & error, have found that specifying a text filter Equal to`Null` (without the quotes) works for text boxes. Does not work for combos however...

 

[Galaxiom]

If one right-clicks on a text or combo box on a form, a pop-up menu appears that gives choices for sorting or filtering on the text or combo contents. One of those options is "Text Filters".

What you are describing is the functionality available in a table or query from A2007 onwards. I might be available on a form in 2013 but I have never seen that.

Otherwise is it that you have a subform where the SourceObject is a table? If that is the case then you are stuck with what is presented to you because there is no Expression facility.

Better to display the records as a form which will provide you with the Filter property.

 

[NoSmoke]

What you are describing is the functionality available in a table or query from A2007 onwards. I might be available on a form in 2013 but I have never seen that.

Otherwise is it that you have a subform where the SourceObject is a table? If that is the case then you are stuck with what is presented to you because there is no Expression facility.

Better to display the records as a form which will provide you with the Filter property.

The records are being displayed as a form (not a subform). Using 2010 BTW. The text filter works as expected with text boxes or combos that are displaying text for the specific record displayed on the form. As I noted in the edit above, specifying `Null` works for text boxes with null values but it does not work for combos. Maybe there is no way of doing it short of using a query.

 

[Galaxiom]

The records are being displayed as a form (not a subform). Using 2010 BTW.

Please post a sample of your form. I have never come across a way that can be done before. I also use A2010.

 

[Pat Hartman]

The following two expressions are effectively the same as those Pat posted.

Call me old-fashioned but I like to be explicit.

 

[nanscombe]

When right-clicking on an empty Combo-box you get the option of "Equals Blank". If you then look at Advanced filter -> Filter by Form it has placed the value of Is Null or "" in the filter.

Unfortunately, for the Combo Box at least, the ordinary Equals option always wraps double quotes (") around the filter so if you write Is Null it automatically changes it to "Is Null".

The only ways I have found to filter it are:

a) With the cursor in the field, Click the Filter button and uncheck all but the (Blanks) option

b) Use the Advanced filter -> Filter by Form option and enter Is Null or ""

c) Sort the field Ascending, then right-click and choose Equals Blank

 

[NoSmoke]

Nigel, thanks much for the explanations and suggestions. I was hoping to find a method that did not require the ribbon and c) is close enough to a good solution.