vtandi
asked on
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???????
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???????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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??????
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??????
vtandi
Are you sure the payments field is a numeric field, not text? If text, it may not be able to identify > 0.
Are you sure the payments field is a numeric field, not text? If text, it may not be able to identify > 0.
ASKER
Both Payments.payment
and Cash.cash are currency fields
-a
and Cash.cash are currency fields
-a
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
mlmcc
ASKER
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....
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....
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
Crystal simply stops execution and doesn't really pass a value back.
mlmcc
ASKER
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)?
if {Cash.Cash} > 0 Then 1
else
if {Payments.Payment} > 0 Then 1
else 0;