Search code examples
ms-accessvba

Textbox null problem

I have a textbox and a button on my Access form. In the click event of the button i want to see if the textbox is empty, if it is, nothing will be executed. So i use 

If Me.textbox.Value = Null Then
    Exit Sub
End if

But it doesn't work... I checked the textbox.value in the execution window and it is Null, but the if clause just doesn't work... Why?

EDIT: @Dimse, I tried "", doesn't work. And also textbox.text = Null, it pops an error telling me the textbox is not active.. Very strange.

Solution

  • Null is never equal to anything, not even Null. Use the IsNull() function.

    If IsNull(Me.textbox.Value) Then

    If you want Me.textbox treated the same when it contains an empty string as when it's Null, concatenate an empty string to it and check the length of the combined string:

    If Len(Me.textbox.Value & "") = 0 Then

    You could also use the named constant, vbNullString, instead of the string literal, "", for an empty string. 

    If Len(Me.textbox.Value & vbNullString) = 0 Then

    Using the string literal requires VBA to construct that string from scratch each time. With the named constant, VBA only needs to reference it, so should be faster and use less memory. However in many (probably most) cases, the performance advantage with vbNullString would be so minor that you wouldn't notice the difference. Also see the comment below from David-W-Fenton.

    For me, the more compelling reason to use vbNullString is that it's instantly recognizable to my aging eyes. Conversely, with the string literal, it takes (a tiny bit) longer for me to confirm that "" is not actually something else ... like " " or "'". The only downside with vbNullString, IMO, is that requires more typing than "".

    And finally, although you don't actually need to explicitly reference the Value property (since it's the default property of a text box), I left it in because you had it that way and because I prefer to be explicit with Value, too. :-)