• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

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???????
 
0
vtandi
Asked:
vtandi
  • 4
  • 3
  • 2
1 Solution
 
mlmccCommented:
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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now