Comparing object.Value = Null does not produce expected results

Question

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

Answer 1

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)