Problem with conditional formula

[jfokas]

I'm having trouble getting the formula below to work properly. Fields {a} and {b} are string fields and field {c} is numeric field. When {a} and {b} are null and {c} has a value, I want to see only "{c}". Instead I get "-{c}", which is consistent with OPTION 3. I've verified that {a} and {b} are indeed NULL and yet I do not get the desired OPTION 1. Can anyone tell me what is wrong with the logic in my formula?

IF ({c}<> 0 AND ISNULL({a}) AND ISNULL({b}))
    THEN (ToText({c})) --OPTION 1
ELSE IF ({c}= 0 )
    THEN ({a}+{b})     --OPTION 2
ELSE ({a}+{b}+ "-" +ToText({c})) --OPTION 3

 

[TheBlondOne]

Have you tried putting the Isnull() first instead of {C} <> 0, I believe that Isnull's need to be evaluated first in nearly all cases.

HTH

 

[jfokas]

Yes, I even tried removing the "{c} <> 0" entirely just to see what would happen and I still got the same result. I've run the query directly against the SQL database and I definitely get NULL for both of those fields when I expect to but still I get Option 3 for my output.

 

[kskid]

Test the nulls first

IF ISNULL({a}) AND ISNULL({b}) and {c}<> 0
    THEN ToText({c}) //OPTION 1
ELSE IF {c}= 0
    THEN {a}+{b}     //OPTION 2
ELSE {a}+{b}+ "-" +ToText({c}) //OPTION 3

What should happen if either {a} or {b} is null? The above only checks for both {a} and {b} being null.

 

[TheBlondOne]

Have you tried converting {C} totext before evaluating, i.e
totext({C},0,"") <> "0

 

[jfokas]

I've tried that variation and still get the same result.

If either {a}, {b}, or both are valued, then I want Option 3 which does indeed work. Option 2 also works when it should. All, my problems are with Option 1.

 

[TheBlondOne]

Might be going over the same stuff here but have you tried the following

if isnull({a}) and isnull({b}) and {c} <> 0 or
{a} = "" and {b} = "" and {c} <> 0 THEN ToText({c})

It might be that even though they appear null they may just be blank strings.

 

[kskid]

I would go one step further eith the trim function in case there are spaces in the field.

if 
(
 isnull({a}) and isnull({b}) and {c} <> 0
) or
(
 trim({a}) = "" and trim({b}) = "" and {c} <> 0
) THEN
 ToText({c})

 

[jfokas]

I'm not really sure why, but {a} = "" and {b} = "" worked. The query against the database specifically returns "NULL". Anyway, problem is solved. Thanks for the help!

 

[kskid]

Correct me if I am wrong but if your have Convert NULL field to default checked in File->Report Options, then your strings will be ""

-LW

 

[TheBlondOne]

SQL works slightly different to Crystal in the way that it handles null values. In SQL it will return null but in Crystal it can either be Null or "". Like wichitakid said if you convert null fields to default then null values will be "" so this should save you time in future.

 

[Madawc]

Regarding nulls, Crytal formulas will stop if they encounter a null, unless an isnull test is done. That's why they always have to be tested for before any other tests are done.

Having come to Crystal from mainframe languages, I got a 'cultural shock' when encountering null. It means 'no data': Mainframe languages mostly treat this as the same as zero.
It is actually a finer shade of meaning, the difference between 'Yes, we have no bananas' and 'I don't know how many bananas we have, it could be some, it could be zero'. In Crystal, the entry is 0 or null and can be tested for.
Without an IsNull, Crystal assumes that anything with a null means that the field should not display.

Madawc Williams (East Anglia, UK)