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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help using a null field as a zero amount

Status
Not open for further replies.

stcholly

Technical User
May 17, 2006
25
US
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top