help using a null field as a zero amount

[stcholly]

Windows XP, Crystal 10, Timberline database.

The following formula, in instances where AP costs nor JC costs have occured, is coming back with a 'blank' value vs. a zero. I'm having difficulty writing the formula to return a zero value in instances where an AP cost transaction nor a JC cost transaction exists.

If {CURRENT_JCT_TRANSACTION.Transaction_Type} in ["AP cost","JC cost"]
and {CURRENT_JCT_TRANSACTION.Cost_Code} <> "17-100" or {CURRENT_JCT_TRANSACTION.Cost_Code} <> "17-200"
and {@Costs Cutoff} = "y" and {CURRENT_JCT_TRANSACTION.Transaction_Type} <> "Dollars paid"
Then {CURRENT_JCT_TRANSACTION.Amount}
else 0.00

Other formula used: If {CURRENT_JCT_TRANSACTION.Accounting_Date} <= {?Cutoff Date} and
{CURRENT_JCT_TRANSACTION.Transaction_Date} <= {?Cutoff Date}
then "y"

I appreciate any help you can provide!
Thanks,
Holly

 

[synapsevampire]

A quick fix is to usE File->Report Options->Convert null value to default.

The other is in the formulas, of course your second would be silly to return a zero, it returns Y normally, so the alternative wouldn't be a zero:

If isnull({@Costs Cutoff})
or
not({CURRENT_JCT_TRANSACTION.Transaction_Type} in ["AP cost","JC cost"])
and
{CURRENT_JCT_TRANSACTION.Cost_Code} in ["17-100","17-200"]
and not({@Costs Cutoff} = "y") and
{CURRENT_JCT_TRANSACTION.Transaction_Type} = "Dollars paid"
Then
0
else
{CURRENT_JCT_TRANSACTION.Amount}
else 0.00

The key is to check for isnull as the first thing in the formula.

Not sure if I have the logic correct above, but use ISNULL()

-k

 

[stcholly]

The "quick fix" did it. I never had to use that before, so I didn't realize it was there. I appreciate the kind response to my 'very newbie question!!!!

Holly