Hello All,

I am new here and was wondering if anyone has ever had the following happen:

When you 'Filter by Selection' in a table for NULL values the number of records shown are less than the actual number of records with NULL values.
For example, I tried this with a particular tables looking for NULLL records in a particular field and while I could clearly see that the number of NULL values was x, the number returned upon filtering on NULL in that field was less than x.

Any idea? Your help is very much appreciated.
Thanks!

Are you sure the field is Null, rather than a ZLS (zero length string)?

The settings of that particular field allows it to be a ZLS but how can I check whether a record is ZLS vs. NULL? When I look at th data I see 27 NULL cells but when I filter on a NULL cell I only get 13 records.

You can't tell them apart visually. Try a query that includes something like this and see what it tells you:

TestField: IIf([FieldName]="","ZLS",IIf(IsNull([FieldName]),"Null",""))

Thanks so much for your help! You were right, the 13 were NULL and the rest were ZLS! How can I change them all to be NULL?

One way would be an UPDATE query:

UPDATE TableName
SET FieldName = Null
WHERE FieldName = ""

Then it sounds like you want to change that setting.

there is an nz function that turns a null into something of your choosing

so if you turn a null into a zero length string, then you only need to test for a zls

hence you can say

if nz(myfield,vbnullstring) = vbnullstring then etc

which conveniently handles EITHER a NULL OR a ZLS at the same time

vbnullstring is short for """", a zero length string, and is probably safer to use than explicitly """