IsNull vs. Len() (1 Viewer)

[MikeAngelastro]

I have read that it is better to use Len() than IsNull() because it is faster. However, it seems that there are times when the variable being evaluated is actually Null, Len() = Null and not zero. In fact, I believe I got this advice from the famous VBA Handbook. But the Access help says that Len(Null) = Null. This intrigues me. When then would it be appropriate to use Len() to test for Null? Anyone have any thoughts on this?

Thanks.

Mike

 

[ghudson]

If Nz(x) = "" Then

...might be more efficient?

 

[WayneRyan]

Mike,

I'd use ghudson's example. The Len function might return a number or
Null. You'll have to "retest" its return anyway. Unless you have a lot
of people banging on your database, or extremely large tables, just
use the Nz function.

You probably won't notice any performance difference.

Wayne

 

[Pat Hartman]

The IsNull() function is intended to determine if a field or variable is null. If you compare something to a null value, the result is null. So If null = null is null.

It depends on what you want to do if the field is null whether the Nz() function will work for you. The Nz() function takes any null value and changes it to whatever you want. The default value returned if you don't specify what you want will be 0 if the field is numeric and "" if the field is text. "" is a zero-length string which is often confused with null. They are NOT the same and they are NOT interchangable. If the field you are testing contains a zero-length string, that's what the Nz() function will return. So if strYourField = "" then, Nz(strYourField, "N/A") will return "" rather than the "N/A" value that you might expect.

My suggestion is that if you use Nz(), ALWAYS specify the value you want returned when the field being tested is null.

So Nz(strField, "N/A") or even Nz(strField,"") is better than the lazy Nz(strField).

For a numeric field
Nz(numField,0) is preferred to Nz(numField)

You could use the following to cover all bases.
If Nz(Len(Me.YourField),0) = 0 Then ' the field is empty or null

 

[ghudson]

I am sure that there is a lot of room for a debate on this issue. I use the following for when I am testing if a field is Null or an empty string [incase a user keyed a value in a field and then cleared it]. I believe this produces the same result but I am a visual person and I also like to separate each test since each situation is different.

If IsNull(X) Or X = "" Then

I would throw in Or X = 0 if I were testing a default numeric value if needed. I guess there might be a slight performance hit if I were testing 500K records using my method above.

 

[Pat Hartman]

I doubt that it would matter enough to worry about. I tend to write the two separate conditions as you have in your second post. I Used the Nz() because your first suggestion was misleading.

 

[MikeAngelastro]

Thanks for all the intelligent ideas. Now I know why Null is the programmer's worst enemy.