7

So I have a frustratingly simple issue that I cannot seem to solve. 

If Me.Bank_Credit.Value = Null Then
Me.Bank_Credit.Value = 0
End If

Basically, I have an unbound box that the user enters data into and then hits a button. After a YES on confirmation box, the data on the unbound box is copied over to the bound box. However, if the user does not enter anything, that in turn creates an empty bound field which can seriously screw up queries down the road.

That being said, the above code simply will not work for me. If I set, for instance, If Me.Bank_Credit.Value = 1 and then run it, the 1s get turned into 2s, as should happen. But it simply refuses to work for Null or even "".

I'm so sure there is a simple solution to this issue, I just can't figure it out.

Thanks in advance

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
user1706975
  • 325
  • 2
  • 5
  • 11

2 Answers2

17

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

When Bank_Credit is Null, the following expression will return Null ... not True as you might expect, or even False.

Debug.Print (Me.Bank_Credit.Value = Null)

It's the same reason for this result in the Immediate window:

Debug.Print Null = Null
Null

Use the IsNull() function.

If IsNull(Me.Bank_Credit.Value) Then

Also, look at the Nz() help topic to see whether it can be useful. You could do this, although it's not really an improvement over IsNull(). But Nz() can be very convenient for other VBA code.

Me.Bank_Credit = Nz(Me.Bank_Credit, 0)
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • 4
    When it comes to Nulls, '=' cannot be used as a comparator ( if x = Null then) but can still be used as an assignment instruction (x = Null). – Philippe Grondier Dec 13 '12 at 21:55
4

HansUp's answer is right, but I thought it relevant to add that there is a similar construct for "Nothing" which is basically a VBA keyword for a dereferenced object. You have to use statements like

If myRange is Nothing Then

You will see these sort of statements all over the VBA help files (and actually in other languages that have a keyword similar to this).

Poisson Aerohead
  • 310
  • 1
  • 9