IsNull vs. Len()

[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

 

[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.

 

[MikeAngelastro]

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