13

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?

D347HxD
  • 375
  • 4
  • 9
  • 26
  • 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. – PMF Nov 12 '13 at 12:33
  • Don't you want to check whether the combobox is _checked_? `If ProjectAddAllDueDateAutoCmBx.Checked Then ...` – CompuChip Nov 12 '13 at 12:33
  • PMF; what would I replace with null for no data then? Compu; I think you have comboboxs mixed with check/option buttons? – D347HxD Nov 12 '13 at 12:35

5 Answers5

24

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
Community
  • 1
  • 1
HansUp
  • 95,961
  • 11
  • 77
  • 135
5

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

Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • 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. – D347HxD Nov 12 '13 at 12:49
4

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
pteranodon
  • 2,037
  • 1
  • 13
  • 20
  • 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! – D347HxD Nov 12 '13 at 12:51
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.]

Oliver
  • 3,225
  • 1
  • 18
  • 12
0

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

If ProjectAddAllDueDateAutoCmBx Is Nothing Then

....

it hope helps.

ZaoTaoBao
  • 2,567
  • 2
  • 20
  • 28