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?

Premeditate

Null is not the same as "no data". Null means there's no combobox at all, which is probably never true. You only need to decide on the condition whether it has data or not. – Lidstone

Don't you want to check whether the combobox is checked? If ProjectAddAllDueDateAutoCmBx.Checked Then ... – Unpredictable

PMF; what would I replace with null for no data then? Compu; I think you have comboboxs mixed with check/option buttons? – Premeditate

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

Validity

Answer 2

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

Battologize

Thank you! If I could, I would list yours and pteranodon's answers as the answer as well as HansUp though he was the first to answer and I can only mark one so he gets that aha. It worked, much love. – Premeditate

Answer 3

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

Immingle

Thank you! If I could, I would list yours and Heinzi's answers as the answer as well as HansUp though he was the first to answer and I can only mark one so he gets that aha. It does work, and thank you for describing why = Null doesn't work; I'll keep it in mind! – Premeditate

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

Guild

Answer 5

the equivalent of null in VB is Nothing so your check wants to be:

If ProjectAddAllDueDateAutoCmBx Is Nothing Then

....

it hope helps.

Kerekes

wtf...so have data? why dont' you try to debug? or print the number of elemens with ..ListCount.. – Kerekes

In VBA, Nothing, Null and Empty are three different values! – Battologize