Comparison with null

[iainm]

I've just spotted a potentially infuriating facet of access comparison. Apparently, when executing a comparison expression (=, >, <, <> etc), the returned value is null if one of the two operands is null. This is thoroughly useless if you have a table in which you need to perform operations on only the records that have a null value in a certain field. Does anyone know how to compare a null value with a value and get True or false, rather than null?

 

[demoman]

I worked in databases for 15 years before I began using VB and Access. I was appalled by how poorly VB/Access deal with Null. Yes, they do have everything need to make it work, but it is far more difficult than it needs to be. I have found Date Null's to be a real pain in the ...!

I have yet to read the definitive documentation on the subject, but that is due to not being able to find it.

Depending on what you are doing, and where you are doing it, the following comparisons are possible:

if <value> is (not) Null
if <value> = (<>) Null
if (Not) IsNull(<value>)

You can make an assignment statement like:

rs!<column> = Null

Most RDBMS/4GL's allow you to reference Null as a zero length string, &quot;&quot;. Microsoft interputs this as having a value other than Null. You have to test this as:

if trim(len(<value>)) = 0

This is important when using variables. If you are clearing variables as part of housekeeping, they will not be Null again, unless explictly set to Null. If you set a date variable to &quot;&quot;, it's value will be &quot;12:00:00 AM&quot;, or something like that. It is a default value for a BLANK date, but not a NULL date.

Anyway, I have picked up some good info reading this forum. Try a full forum search on Null. There is some good info there, even though I am still waiting for that final 'moment of clairity'. If you have a specific example, please send it on.

Regards
Steve

 

[WildHare]

the returned value is null if one of the two operands is null.

This is ALWAYS going to be the case, and has been in every DBMS/4GL I've ever worked with.

In fact, it's probably an ANSI SQL standard.

One of the reasons for the NZ Function, I suppose...

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[iainm]

Yes, I'm sure it's always going to be the case. But my problem comes from a situation like
a=1
b=Null
If b<>a Then.....
OR
If Not b = a Then...
b is clearly not equal to a, however, neither of these expressions will return true. My point is that null has no place as a result in boolean operations - boolean operations are based on the fact that any statement is either true or false, not &quot;nothing&quot;. I don't understand what makes this use of null reasonable.

 

[FoxProProgrammer]

Access probably treats null in this manner because &quot;null&quot; is defined as an invalid value. It isn't the same thing as &quot;&quot; (an empty string), which is commonly referred to as &quot;null&quot;. In your example, you could use the NZ funtion to return a value for b, and then b <> a would evaluate TRUE like you want.

dz

 

[experienced]

You can discuss philosophical aspects of NULL, nothing, and its meaning for hours. It is a confusing issue and it is not Miscrosoft's fault. One source of confusions comes from the fact that there are variables (in modules) and there are controls (on forms), and you can mix them in your programming even though they are quite different kinds of objects. The practical way is that whenever Null is possible with a variable (variant) or a control, always use Nz function to make sure you don't get an Invalid Null error. If a is NULL, Nz(a) will return &quot;&quot;. In your example, you should write like

If Nz(a) <> Nz(b) Then

or if you want to concatenate strings, which may be NULL, write

strA = Nz(B) & Nz(C)

Also note that NULL is not the same as a null character (=Chr$(0)), as someone mentioned above.

Cheers

 

[experienced]

You can discuss philosophical aspects of NULL, nothing, and its meaning for hours. It is a confusing issue and it is not Miscrosoft's fault. One source of confusions comes from the fact that there are variables (in modules) and there are controls (on forms), and you can mix them in your programming even though they are quite different kinds of objects. The practical way is that whenever Null is possible with a variable (variant) or a control, always use Nz function to make sure you don't get an Invalid Null error. If a is NULL, Nz(a) will return &quot;&quot;. In your example, you should write like

If Nz(a) <> Nz(b) Then

or if you want to concatenate strings, which may be NULL, write

strA = Nz(B) & Nz(C)

Also note that NULL is not the same as a null character (=Chr$(0)), as someone mentioned above.

Cheers

 

[rajeessh]

a=1
b=Null
If IsNull(b) or IsEmpty(b) or Len(b)=0 or b<>a Then.....

The above may work out
Access has the habit of gicing IsNull as true only if the
b is null
If it is empty or len as zero we get negative reply for IsNull