null fields in formulas

[smgs]

If I have 2 columns and one of them is null and I use them both in the same formula, for example,

formula = col_1 + col_2, then I get a null result when col_ 2 is null even if col_1 is not null.

When they are both not null, I get a result.

I have tried to test for null as the beginning of the formula: if isnull({col_2) then.... but this does not seem to work. So I have 2 questions:

Am I doing something wrong?
Do I have to test for null in every formula where a field might be null?

 

[dgillz]

I always test for nulls if there is a possibility of a null value. As to what you are doing wrong, you need to post your entire formula so we can see.

Another alternative is under file, report options, the "convert null field value to default" is checked on. This can sometimes return strange values, especially with date fields so you will definately want to test this and make sure you want this option turned on. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Ngolem]

you should post the exact formula....not a fake one to get a proper answer...But I can Guess

you must check both "columns" for being null...I suspect that you are doing it out of order. You can do it a number of ways...I would probably do it this way in your case


@SummingColumns

WhilePrintingRecords;
//this assumes a new total..you could have a reset somewhere
//if this was a running type of total
//then it would be just: NumberVar Total;
NumberVar Total := 0;

if not isnull({Table.col1}) then
Total := Total + {Table.col1} ;

if not isnull({Table.col2}) then
Total := Total + {Table.col2} ;

Total;

Jim Broadbent

 

[Naith]

To answer your last question, yeah - I would use null catchers like the chaps above suggested.

However, you say that you tested for nulls using isnull to no avail. I don't know if your col1 and 2 are character strings, but some databases consider a string field value you would say is null to be = '', which is different to null.

You should try catching both possibilities in your formula.

If I may pinch the concept Ngolem's formulae, and use it for a string example, you would test for '' by adding:

if not isnull({Table.col1}) then
if {Table.col1} = ''
then "NULL"
else {Table.col1}
else
"NULL"
+ " " +
if not isnull({Table.col2}) then
if {Table.col2} = ''
then "NULL"
else {Table.col2}
else
"NULL"

You want to make sure that Col1+Col2 don't spill over the 254 character limit.

Naith

 

[dgillz]

To followup on Naith's comment, I have had inconsistent results using IsNull(), especially with btrieve databases. I have used the following technique more than once:

If IsNull({Field}) or Length({Field})=0 then <<Result1>>
else ......

Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com