Filter By Selection error for blank fields

[td]

I have a large table of data. I am viewing the data in a front-end form, and
have the data sorted by one particular text field that I am interested in. I
want to filter to only the records for which that field is blank, so I select
that blank field in one of the records, right-click, and select Filter By
Selection.

The results are bad. I can clearly see the approximate number of records
that have this field blank (roughly 100), but the Filter By Selection only
shows a portion of them (roughly 60). I have made sure there are no spaces
in the fields. I have even copied one field and pasted it into each of the
other blank fields (in case there was some non-real character that might be
lurking in the field), but the filter still only finds some of the fields!
Very very confusing.

Any ideas what my issue might be? This really concerns me, as I have
reports that rely on Is Null criteria, and I'm wondering if they are really
picking up all of the data I need to see.

 

[Arvin Meyer [MVP]]

Try creating a query and use the criteria:

Like "" or Is Null

and see how many results are returned. If you get all of them, you won't be
able to filter properly using Filter By Selection, because there are 2
different criteria, an empty string or a null.

 

[Ken Sheridan]

I have a large table of data.  I am viewing the data in a front-end form, and
have the data sorted by one particular text field that I am interested in..  I
want to filter to only the records for which that field is blank, so I select
that blank field in one of the records, right-click, and select Filter By
Selection.

The results are bad.  I can clearly see the approximate number of records
that have this field blank (roughly 100), but the Filter By Selection only
shows a portion of them (roughly 60).  I have made sure there are no spaces
in the fields.  I have even copied one field and pasted it into each ofthe
other blank fields (in case there was some non-real character that might be
lurking in the field), but the filter still only finds some of the fields!  
Very very confusing.

Any ideas what my issue might be?  This really concerns me, as I have
reports that rely on Is Null criteria, and I'm wondering if they are really
picking up all of the data I need to see.

The first thing to do is to set the column's AllowZeroLength property
to False in table design view. If you can set the property
successfully then there are no zero length strings in the column. If
not change any to Null with an update query:

UPDATE YourTable
SET YourField = NULL
WHERE LEN(YourField) = 0;

Then try setting the property to False again.

You can now identify the rows with NULL at the column position with:

SELECT *
FROM YourTable
WHERE YourField IS NULL;

If this still omits rows which you expect to be returned then return
all the non-Nulls with:

SELECT YourField
FROM YourTable
WHERE YourField IS NOT NULL;

and set any which look empty to NULL manually by highlighting them and
pressing the Delete key.

The Filter by Selection mechanism should now work as it in fact sets
the form's Filter property to an expression such as:

((YourTable.YourField Is Null))

Ken Sheridan
Stafford, England