using IsNull to filter out null values in the caculation

[Jayson]

Can somebody help me, i'm simply trying to add 4 database fields, some fields may contain null/blank values...
but i can't get my formula to add the 4 fields properly, here's my code :

----------------------------------------------------------
NumberVar var1;
NumberVar var2;
NumberVar var3;
NumberVar var4;

NumberVar Result;

var1 := {sp_SaleProjRpt;1.gross_q1};
var2 := {sp_SaleProjRpt;1.gross_q2};
var3 := {sp_SaleProjRpt;1.gross_q3};
var4 := {sp_SaleProjRpt;1.gross_q4};

If IsNull ({sp_SaleProjRpt;1.gross_q1}) Then
var1 := 0
Else If IsNull ( {sp_SaleProjRpt;1.gross_q2}) Then
var2 := 0
Else If IsNull ({sp_SaleProjRpt;1.gross_q3}) Then
var3 := 0
Else If IsNull ({sp_SaleProjRpt;1.gross_q4}) Then
var4 := 0
Else
Result := var1 + var2 + var3 + var4;
Result;

It only gives me a "Result" if all 4 database fields contains value, if at least 1 of the fields does not contain values, then it gives me a blank "Result"

thanks!

 

[kenhamady]

Try:

if IsNull({sp_SaleProjRpt;1.gross_q4}) then 0 else {sp_SaleProjRpt;1.gross_q4} +

if IsNull({sp_SaleProjRpt;1.gross_q3}) then 0 else {sp_SaleProjRpt;1.gross_q3} +

if IsNull({sp_SaleProjRpt;1.gross_q2}) then 0 else {sp_SaleProjRpt;1.gross_q2} +

if IsNull({sp_SaleProjRpt;1.gross_q1}) then 0 else {sp_SaleProjRpt;1.gross_q1} Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

The reason you get no Result when there is a null value in one of your fields is that there is no place that you assign a value to Result - except when no null values are found.
...
Else
Result := var1 + var2 + var3 + var4;
Result;
...change to
Else
0 ;
Result := var1 + var2 + var3 + var4;

That should give you the correct result. Or the solution from Ken - just thought you might want to know where it was breaking down.
cheers,
Malcolm

 

[Jayson]

Thanks guys! you've been very helpful!