Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Number field show null instead of 0 1

Status
Not open for further replies.

veronic

Programmer
Apr 6, 2004
73
US
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???
 
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}
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top