Substitute zero for null in formula? 1

[CraigBest]

I have a formula I need to write which calculates the value of two numeric fields. However, most of the data in one field is nulls, and some in the other. Wherever I have a result that is null I'd like to have a zero (0.00) instead of the null (blank) because the result field of the formula is used later in another calculation.

How can I write this formula to get the results I want?

Thanks

Craig in NJ

 

[synapsevampire]

There are options.

File->Report Options->Convert null values to default. This eliminates nulls from the data returned.

If you need to do NULL checking in the report, then use a formula instead fo the fields:

if isnull({table.field1}) then
0
else
{table.field1}

Now use the formula in your calc, or you could just embed this into your formula:

if isnull({table.field1}) then
0
else
{table.field2}-{table.field1}

Since you didn't share the formula I couldn't give the exact syntax.

-k

 

[CraigBest]

Thanks for the response -- the report options idea seems to have done the trick, but I appreiciate the examples too.

Funny, when I tried that example earlier (I wrote exactly the same statement and tried it out before I posted) it didn't work. It looked perfectly correct to me too -- I wonder why it failed?

Oh well, doesn't matter now, the report works. Thanks.

 

[Madawc]

Did you try using the field before testing it for null? I did that when I was starting with Crystal, before learning that it always stops on a null, even if there is a subsequent isnull test.

Madawc Williams (East Anglia, UK)