Number field show null instead of 0 1

[veronic]

I am using Crystal 9.0, Oracle DB.
I have interesting problem. When I just drop a field (number) from db it normally show 0 when it is no amount but if I take this field and create a formula:

if {FACTS_REPORTS_MAILOUT.RECEIPT_ADJ_TYPE} = "R" then
{FACTS_REPORTS_MAILOUT.AMOUNT}
and drop it on the report I receive null if it is no amount
Why???

 

[midearth]

Crystal handles null values strangely at times. Try adding a check for null values in your formula such as:

if {FACTS_REPORTS_MAILOUT.RECEIPT_ADJ_TYPE} = "R" and IsNull({FACTS_REPORTS_MAILOUT.AMOUNT}) then "0" else
if {FACTS_REPORTS_MAILOUT.RECEIPT_ADJ_TYPE} = "R" and not((IsNull({FACTS_REPORTS_MAILOUT.AMOUNT})) then{FACTS_REPORTS_MAILOUT.AMOUNT}

 

[synapsevampire]

There are different solutions:

You can use the File->Report Options->Convert Database null values to Default to make all null values come in as the default (numbers = 0, strings = "") so you don't have to code.

Or you can code for it, as in:

if {FACTS_REPORTS_MAILOUT.RECEIPT_ADJ_TYPE} = "R" then
(if IsNull({FACTS_REPORTS_MAILOUT.AMOUNT}) then
0
else
(IsNull({FACTS_REPORTS_MAILOUT.AMOUNT}
)
else
0

Note that your formula didn't allow for the instance where the receipt_adj_type did not equal R, which was probably what was showing as the null value, not the field itself.

You may have been fine with:

if {FACTS_REPORTS_MAILOUT.RECEIPT_ADJ_TYPE} = "R" then
{FACTS_REPORTS_MAILOUT.AMOUNT}
else
0

One or the other will work, you need to know the way the data is stored to know for sure (allows nulls).

-k

 

[veronic]

Thank you very much. Works!