Combobox null in if statement

Question

I am trying to code an if statement where if a certain combobox is null, then it runs a certain part of code if it has data in it then it runs another. I wrote up this:

Private Sub ProjectAddSetDateAutoBtn_Click()
If ProjectAddAllDueDateAutoCmBx = Null Then
'Code1
Msgbox("ComboBox Is Null")
Else
'Code2
Msgbox("ComboBox Has Data")
End If
End Sub

I leave the combobox with no data, and then it doesn't run the code in the first part of the if or the code in the 2nd part of it either! If I enter data into the box, it runs the 2nd part of the if statement perfectly. There are no errors, I am quite stumped on this. Do ComboBoxes have their own "Null"? Is there a problem with this if statement?

Answer 1

Nothing is ever equal to Null, not even another Null.

Use IsNull() to check whether the combo box is Null.

'If ProjectAddAllDueDateAutoCmBx = Null Then
If IsNull(ProjectAddAllDueDateAutoCmBx) = True Then

Answer 2

You cannot use a = Null comparison to get the results you want because Null propagates. To see this in action, try:

? Null = Null

in the Immediate Window and you'll see that Null is returned. Use the IsNull function, which will return true or false as you would expect.

Private Sub ProjectAddSetDateAutoBtn_Click()
If IsNull(ProjectAddAllDueDateAutoCmBx) Then
'Code1
Msgbox("ComboBox Is Null")
Else
'Code2
Msgbox("ComboBox Has Data")
End If
End Sub

Answer 3

I would suggest

If IsNull(ProjectAddAllDueDateAutoCmBx.Value) Then

It correctly checks for Null (IsNull instead of = Null), and it explicitly checks the value of the combo box.

(In most cases -- depending on the context -- just using the name of the control yields the value, but it doesn't hurt to be explicit.)

Answer 4

While the accepted answer is totally correct, I use a different approach:

If HasValue(ProjectAddAllDueDateAutoCmBx) Then

where the HasValue function is:

Public Function HasValue(v As Variant) As Boolean
    If Trim(v & "") <> "" Then
        HasValue = True
    Else
        HasValue = False
    End If
End Function

This has the advantage of treating NULL and "" (or any pure whitespace) values the same, which is many times what you want with MSAccess controls. For example entering a value in a null-valued textbox and removing it again with backspace will result in a ""-value, not NULL. From a user-perspective this is mostly meant to be the same.

[The (v & "")-part is just a trick to force conversion to a string.]