I am trying to create a formula field in crystal reports combining an if, then, else with an OR. This seems as if it should be simple, but I am stumped.

I am trying to write a simple IF X or Y then Z else W statement in crystal and while it seems like it should be a simple thing, I cannot get it to work:

formula field ( GiftCount) Code reads:
if {Cash.Cash} > 0 OR
{Payments.Payment} > 0
Then 1
else 0;

a second field (Sum) reads:
sum({@GiftCount})

The problem is sum totals the 1's for all the records where Cash.Cash > 0 but does not include any 1's in the records where Payments.Payment > 0.  Using the built in summary field returns the same result leading me to assume that the field value is not being set to 1 for those later records.

What am I missing here???????
 
vtandiAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
I suspect the problem is you have NULL values in the fields.

Try this
If Not IsNull({Cash.Cash})  AND {Cash.Cash} > 0  then
    1
else If Not Is Null({Payments.Payment}) AND {Payments.Payment}  > 0 then
    1
else
    0;

mlmcc
0
 
sjjcatCommented:
Try:
if {Cash.Cash} > 0 Then 1
else
if {Payments.Payment} > 0 Then 1
else 0;
0
 
vtandiAuthor Commented:
Thanks sjjcat, I tried that one (Try:
if {Cash.Cash} > 0 Then 1
else
if {Payments.Payment} > 0 Then 1
else 0;)
and it did not work

mlmcc - I will try yours in the AM.  However, if I create the if/then without the OR (i.e. If Payments.payment >0 then 1 else 0;), it DOES count them as I would expect.  It is only when I add the OR that it seems to want to not work.  I think the fact that each part works individually would suggest that it is not the Null thing but rather something to do with the OR??????
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sjjcatCommented:
vtandi
Are you sure the payments field is a numeric field, not text? If text, it may not be able to identify > 0.
0
 
vtandiAuthor Commented:
Both Payments.payment
and Cash.cash are currency fields

-a
0
 
mlmccCommented:
If Payments can be NULL when Crystal uses the field it stops execution and returns some value probably NULL or no value thus it doesn't count.

mlmcc
0
 
vtandiAuthor Commented:
mlmcc:

Thanks so much.  Just got back to this and your code did the trick.  Not sure why I have so much trouble writing Crystal formulas as opposed to Access or Excel....
0
 
mlmccCommented:
Probably because of the way Crystal handles NULL values in a formula.

Crystal simply stops execution and doesn't really pass a value back.

mlmcc
0
 
vtandiAuthor Commented:
Any general tips for an Advanced Excel and Access user who is finding Crystal formulas to be a painful experience (despite a large George Peck reference volume)?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.