CODEWITHSUNDEEP
vbams-access
user1706975
user1706975

Reputation: 325

Comparing object.Value = Null does not produce expected results

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

Upvotes: 7

Views: 623

Answers (2)

HansUp
HansUp

Reputation: 97101

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)

Upvotes: 17

Poisson Aerohead
Poisson Aerohead

Reputation: 360

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

Upvotes: 4

Related Questions