Curious about NULL

[wfs]

During class last night we encountered something that I am curious about and I’m wondering if anyone here has the answer.

The code snippet below is what is causing the dilemma.

***********
Do Until adorecordset.EOF
'If adorecordset!Name <> Null Or adorecordset!Name <> "" Then
If Not IsNull(adorecordset!Name) Then
List1.AddItem adorecordset!Name
End If
adorecordset.MoveNext
Loop
***********

The commented out IF test is what my instructor gave us.

The OR part of the IF statement is required for the body of the IF statement to be executed.

The ISNULL function does not have this glitch.

Logically the “ <> Null ” and the “ Not IsNull()” are the same. RIGHT??

The database is BIBLIO.mdb.

Any thoughts??

[Buzby]

NULLs are indeed curious things.

1: NULLs are not the same as nothing.

ie NULL is not equal to ""
This is because a NULL is in fact 1 character (control code 0). Therefore it is not the same as "" which is 0 characters.

2: You can't use IF [var]=NULL

'cos it doesn't work. You MUST use IF ISNULL([var]) or IF NOT ISNULL([var]). Despite what Microsoft claim in their user guides and help files IF [var]=NULL never seems to work.

And to quote from the help file;

Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False.

.. and if that's supposed to make sense I don't want to be sober.


In databases you can avoid the dreaded NULLs when reading records by doing this;

Dim MyVar As String
MyVar=Recordset.Field!fieldname & ""

If fieldname is NULL MyVar returns an empty string (if you left the & "" off the end you would get an error)

Hope this makes some sense.