Sum of fields which may contain null

[kutoose]

I am adding crating a calculated filed, @profit, which is equal to field1 + field2 - field3. Now the issue is at some places field1 or field 2 or field3 may be null. ( It can be all fields will be null, or just one of the field is null). In such cases the calculated field is blank. If a field is null, I would like to assign it as 0.

Please help...

 

[Reebo99]

File > Options > Reporting

Check convert nulls to default

Let me know how you get on.

Reebo
Scotland (Sunny with a Smile)

 

[kutoose]

Sorry - It is not working all the time. Any other way ?
Thanks Reebo99.

 

[Reebo99]

The only other way is to check for a null value bfore performing your calculation :

If Isnull(Field1) = True then NumberVar Field1Calc := 0 else NumberVar Field1Calc := Field1;
If Isnull(Field2) = True then NumberVar Field2Calc := 0 else NumberVar Field2Calc := Field1;
If Isnull(Field3) = True then NumberVar Field3Calc := 0 else NumberVar Field3Calc := Field1;

Field1Calc+Field2Calc+Field3Calc

Or something like that......Not at CR at the mo, but you get the idea.

Oh, and remember to turn of the convert nulls again.

Reebo
Scotland (Sunny with a Smile)

 

[Reebo99]

The only other way is to check for a null value bfore performing your calculation :

If Isnull(Field1) = True then NumberVar Field1Calc := 0 else NumberVar Field1Calc := Field1;
If Isnull(Field2) = True then NumberVar Field2Calc := 0 else NumberVar Field2Calc := Field2;
If Isnull(Field3) = True then NumberVar Field3Calc := 0 else NumberVar Field3Calc := Field3;

Field1Calc+Field2Calc+Field3Calc

Or something like that......Not at CR at the mo, but you get the idea.

Oh, and remember to turn of the convert nulls again.

Reebo
Scotland (Sunny with a Smile)