IsNull() in an OR statement - do I need it. 2

[grnzbra]

I have the following IF statement.

If FinanceCharge > 0 OR IsNull(FinanceCharge) = False Then

Process the FinanceCharge

End If

The desired result is to process the finance charge only if there is a non-zero value in it. This works fine if the finance charge is null, but if it is 0, the IsNull(FinanceCharge) is false which means that the IsNull portion evaluates to true and the 0 is processed.
Simply removing the IsNull() portion of the IF statment seems to correct the problem. However this is a very complex program written by someone who, obviously, knew what they were doing (they must have had a bit of brain stutter here), so I'm wondering what the purpose of the IsNull portion could have been. Does anyone have any ideas about why this might have to check for nulls?

 

[MarrowGenx]

The purpose of an isnull is to catch any variable or object with a null value. Null does not mean "0", it means blank. If a variable or object has not been assigned a value, then it carries a null value. The reason it is in the code is to insure that all variables have valid information before continuing the process.

In your case, this expression catches only variable values of positive numbers. 0, -1, and Null are all values that are excluded in this equation.

Hope this helps.

Marrow

 

[allanon]

That if statement should more likely be:

If Not(IsNull(FinanceCharge)) and (FinanceCharge > 0) Then

Process the FinanceCharge

End If

The reason for this is that the null condition is tested first. If the field fails the null test then it will not test for > 0. This is what I think the person was after when they wrote this.

I have one small opinion statement to make here about your above comment 'However this is a very complex program written by someone who, obviously, knew what they were doing '. I have been programming computers for 15 years now and I will let you know that just because a person made a program complicated does NOT mean they knew what they were doing. The company I work for paid $250,000 for an access application that was very complex. I told them not to buy it and let me develope something but they would not listen. I spent the next six month rewritting the application because it just was not reliable. More often simple is better and more reliable and I think any experience programmer will agree with that.

Sorry for rantting. I hope I have answered your question and feel free to ask more.

 

[grnzbra]

But why is it NECESSARY to check for the null. If the other part of the statement is true (financecharge > 0) then it isn't null. When I first saw it, I thought that perhaps the FinanceCharge > 0 might generate an error if it was fed a null value, but I tried that on a simple 1 table and a few lines of code database and found that the "greater than" statment handled nulls and zeros fine.

This seems to fall into just what you were talking about; complex ain't a sign of knowing what you're doing and adding another "something" that could coded incorrectly.

 

[allanon]

I agree. When you are dealing with different data types null are handled differently.

Just for fun I set up a record with this data:

KeyField TextField NumberField CurrencyField DateField BooleanField DummyField
1 0 $0.00 No Dummy1
2 0 $0.00 No Dummy2
3 0 $0.00 No Dummy3

The dummy field allows me to go to the next record without entering anything in the other fields. As you see most of these get initialized if you put no value in them. Testing these type for a value cause no problems. What I have found is that most ofter I have errors if I try to move a null string into a string. This can happen mostly when passing paramaters to functions and stuff like that. It is never a bad thing to test for null but doing it after you test for a value is like trying to drive a car before you put gas in it. It does no good. If the null was going to cause a problem it would error on that if statement.

Just remember that a string that contain null is not the same as an empty string. The first can cause errors the second will not.