refresher on VB6 Null handling

[BruceG]

Hello -
I am tasked with updating a legacy VB6 process that writes out a text file from a SQL Server table. This program uses equal / not equal operators to test for Null, which I always thought was a no-no. (I always used the IsNull function.)

They do things like:
If rsTable!SomeField = Null Then ...
and
If rsTable!SomeField <> Null Then ...

The "=" seems to be OK, but the "<>" seems to be causing a problem (i.e., the output is always empty for the field being tested with "<> Null").

Before I do the surgery, I'd like a second opinion, or confirmation on this: "FieldName <> Null" will always return False, whether or not the database field is actually null?

Thanks, guys ...

[Siddharth Rout]

I prefer this

If len(trim(rsTable!SomeField)) = 0

or

If len(trim(rsTable!SomeField)) <> 0

[LaVolpe]

Use IsNull(). Try this little experiment & you might be amazed

Dim MyVar As Variant
MyVar = Null
If MyVar = Null Then
    MsgBox "MyVar is Null? True"
Else
    MsgBox "MyVar is Null? False... Why?"
End If

Here's MSDN's description of IsNull and above example

Edited: You can use the trick koolsid provided, but it doesn't tell you if the field is null or not, only that it doesn't have any data. It could, for example, contain a null string. Depends on what exactly you want to test for.

[BruceG]

Thanks guys. The bottom line is that the equal (=) and not equal (<>) operators should not be used to test if a field is null. The IsNull function really must be used. The folks I inherited this from were obviously not aware.

[vpben]

The fastest possible way to check if a string is null is to do as followed:

If LenB(String_Here) Then Msgbox "Not Null!"
If LenB(String_Here) = 0 Then Msgbox "Is Null!"

Note that while not null it is not necessary to add the <> 0 but it is while checking if null. If Not LenB() would not work.

LenB() is the same as Len() except LenB does not divide the response by two, making it slightly faster.

[GaryMazzone]

vpben did you look at LaVolpe's response.... the use of a Len function (even LenB) does not differenciate between and empty string ('') and a NULL value

[Merri]

The information posted by vbpen is indeed incorrect. LenB only returns the byte length of a String variable. It doesn't make a difference between a null string and an empty string.

The easiest way to check whether a string is null is to use StrPtr – this returns the pointer to the string. If StrPtr = 0 Then string is null.

Just for a note:

• Empty string: MyString = ""
• Null string: MyString = vbNullString

You can check the difference yourself by using StrPtr(MyString). Empty string is an allocated string of zero length (ie. the pointer is non-zero, because there is an allocated BSTR structure). Null string is just a string variable with no data (ie. the pointer is zero because there is no data to point to).


Another note: IsNull does not work with strings. It only works with the Variant datatype.

[vpben]

Yep sorry I did not read the question correctly I guess.

I thought they were just asking to check if the string contained data, not if it was assigned data.

I tried IsNull(var) and it failed to return true without and while setting the variable..?

[Merri]

IsNull works only if it is set as Null. Basically only useful with databases where a field returned can be null. Technically Null is just a datatype of it's own, for example you can check VarType of a Variant variable to see if it is a Null or not.

Dim MyVariant
MyVariant = Null
If IsNull(MyVariant) Then MsgBox "Null"

[dilettante]

There there are Empty and Error subtypes as well.


As usual, the manual spells all this out. For example If...Then...Else Statement says:

Condition

A numeric expression or string expression that evaluates to True or False. If condition is Null, condition is treated as False.

Then Comparison Operators shows that all of the simple comparison operators (<, <=, etc.) return a Null value if one of the operands is Null.