Comparison of the object. Value = Null does not produce the expected results.

Question

So, I have a frustratingly simple problem that I cannot solve.

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

Basically, I have an unconnected box in which the user enters data, and then clicks the button. After confirming YES in the confirmation field, the data in the "Unrelated" field is copied to the associated block. However, if the user does not enter anything, this in turn creates an empty field that can seriously damage requests along the way.

Having said that, the above code just won't work for me. If I set, for example, if Me.Bank_Credit.Value = 1, and then ran it, then 1s will turn into 2s, as it should. But he just refuses to work for Null or even "".

I am sure there is a simple solution to this problem, I just can not understand.

Thanks in advance

Answer 1

Nothing can ever be compared to Null, even to another Null. And nothing is ever compared to Null, even to another Null.

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

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

This is 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, see the Nz() help section to see if it can be useful. You can do this, although this is really not an improvement over IsNull() . But Nz() can be very convenient for other VBA code.

 Me.Bank_Credit = Nz(Me.Bank_Credit, 0) 

Answer 2

HansUp's answer is right, but I thought it necessary to add that there is a similar construct for "Nothing", which basically is the VBA keyword for an object with dereference. You should use expressions such as

 If myRange is Nothing Then 

You will see these instructions for all VBA help files (and in fact in other languages ​​that have a keyword similar to this).