Null Query

[Tony]

i have a query to sort out "is not null" record.

However, i find there is null records in the result.

Then i revert the query to "null", however, there is no record.

apparently the visible "null" records are not null.

then i check through the table, the record has nothing in the field. the
field is a text field.

what's wrong? Thanks.

 

[Mark]

Tony

Are you sure the field is null, and doesn't contain a
space (text field)? Does the field allow Empty strings
("")?

You could look for them with the len() function.

Mark

 

[tony wong]

Thanks Mark

Yes, the field is not allowed empty.

Allow empty is now disabled.

How can deal with these records(not null but nothing visable)? Is there
anything hidden? thx.

 

[Gary Walter]

i have a query to sort out "is not null" record.

However, i find there is null records in the result.

Then i revert the query to "null", however, there is no record.

apparently the visible "null" records are not null.

then i check through the table, the record has nothing in the field. the
field is a text field.

Hi Tony,

One method would be

WHERE
Len(Trim([SomeField] & "")) >0;

instead of

WHERE
[SomeField] IS NOT NULL;

Please respond back if I have misunderstood.

Good luck,

Gary Walter