Solved

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.

Posted on 2008-10-07
9
506 Views
Last Modified: 2012-05-05
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
Comment
Question by:vtandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 50 total points
ID: 22663379
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
 
LVL 1

Expert Comment

by:sjjcat
ID: 22663621
Try:
if {Cash.Cash} > 0 Then 1
else
if {Payments.Payment} > 0 Then 1
else 0;
0
 

Author Comment

by:vtandi
ID: 22663751
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
LVL 1

Expert Comment

by:sjjcat
ID: 22663801
vtandi
Are you sure the payments field is a numeric field, not text? If text, it may not be able to identify > 0.
0
 

Author Comment

by:vtandi
ID: 22664015
Both Payments.payment
and Cash.cash are currency fields

-a
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22664142
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
 

Author Comment

by:vtandi
ID: 22669373
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22669395
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
 

Author Closing Comment

by:vtandi
ID: 31504001
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question