--Is Not Null-- vs -- <>"" --

[adrian.stock22]

--Is Not Null-- vs -- <>"" --

A Query based on ValidMainTable shows 418 records when I set Fax to <>"", as in the SQL condition below

WHERE (((ValidMainTable.Fax)<>"") AND ((ValidMainTable.URL)="") AND ((ValidMainTable.FaxSuppression)="N") AND ((ValidMainTable.LuigiBatchDate) Is Not Null));

but when I set fax to
Is Not Null
1071 records will show

Fax is a text field which is used for storing fax numbers, but is not made a number field because I occasionally may want to write a note into it.

What accounts for the difference in the number of records shown? Or rather, how do the records differ which are being shown?

What is the difference between
<>"" and Is Not Null

Thanks.

Adrian

 

[Banana]

<>"" tells Access to ignore any records that has zero length string.

Not IsNull tells Access to ignore any records that has nothing, not even zero length string.

If you want to make sure it returns only records that has something other than null and zero length string, you need to code as

Not IsNull(Myfield) And Me.Myfield <> ""

.

That will return only records that has a value other than null and zero length.

 

[KeithG]

NUll is an unknown value where as "" is a zero length string. Also I would add a notes field instead of using FaxNum for your notes.

 

[adrian.stock22]

Thanks, Banana and KeithG, I will consider these points - need some more information to understand entirely.

Notes field can be done, but the text presently in some of the Fax cells is "FPS" (Fax Preference Service), which means it is illegal fax these people, and we must not try to obtain their fax numbers from other sources. So the FPS marker constitutes an effective blocking mechanism against someone eagerly going and searching for these deliberately missing fax numbers (as opposed to fields where we accidentally do not yet have the fax numbers).

Would you suggest I convert the fax numbers field into a 'number' field? Would that reduce the potential 'ambiguities' between the two expressions in my subject line?

Banana distinguishes between "nothing" and "zero lenght string". What is the difference?

"zero length string" in text fields, and "nothing" in number fields???

In practical terms, when Fax is set to "Is Not Null", Datasheet View shows some empty cells, some cells with fax numbers, and some cells with the marker "FPS". What I want to see is cells with fax numbers and with FPS, but no "empty" cells.

So what do these "empty" cells contain which causes them to be displayed in spite of the "Is Not Null" selector?

Thanks for your help.

Adrian

 

[Banana]

You would create a zero length string by making an entry then deleting it. That still has a "value" and that is a zero length string. Nul is simply nothing. Nothing has been assigned at all. You could compare it this way: Zero length string is kind like when you divide 0 by 1 which you get 0 whereas Null is what you get when you divide 1 by 0 = indefinite.

I think it may be also created if you give the control focus but does nothing, but someone need to confirm that.

To solve that, go to the table design, and select your field then in properties set "Allow Zero Length" to No. That will eliminate all zero length strings. But as a safety measure you will want to have criteria checking for *both* null and zero length string anyway.