Proper way to check if an unbound control has a value

Question

Simple scenario: a form and one text box (unbound), Text1.

If "" <> Text1 Then
    MsgBox "Not Empty"
End If

The above code works. The expression ""<> Text1 evaluates to True if the text box contain characters.

The opposite doesn't work, regardless of the text box is empty or not:

If "" = Text1 Then  ' or alternatively, False = ("" <> Text1)
     MsgBox "Empty!"
End If

Can you clarify this issue?

Answer 1

The textbox is usually null if it does not contain anything, this is not the same as a zero length string (""). It can often be best to use:

If Trim(Text1 & "") = vbNullString
   'Empty

This will be true if the textbox contains spaces, a zero length string or null.

Answer 2

Alternatively, I've got a small function that I find useful for checking that sort of thing when dealing with various variable types and I just want to know whether it's blank.

'-----------------------------------------------------------------------------'
' True if the argument is Nothing, Null, Empty, Missing or an empty string.   '
'-----------------------------------------------------------------------------'
Public Function IsBlank(arg As Variant) As Boolean
    Select Case VarType(arg)
        Case vbEmpty
            IsBlank = True
        Case vbNull
            IsBlank = True
        Case vbString
            IsBlank = (arg = vbNullString)
        Case vbObject
            IsBlank = (arg Is Nothing)
        Case Else
            IsBlank = IsMissing(arg)
        End Select
End Function

Just Made a blog post today about it too.

Answer 3

I always use the function Nz (variable, valueIfNull) to check those kind of things. For example:

 if (Len(Nz(Me.txt1, "")) > 0) then
        'Me.txt1 does not contain a value
 end if

Or if I want to save a textbox's value to a recordset:

rst!Name = Nz(Me.txtName, "No name given")