Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal Reports XI Formula - Else If Statement Ignored

Posted on 2012-03-22
2
Medium Priority
?
917 Views
Last Modified: 2012-06-27
I created a formula to calculate the number of unique records with data points entered into any of 11 fields.  The other criteria is that the data is counted if the date within any of those fields falls in the date range entered per start and end date parameters.  In other words at least 1 of the 11 fields must have a date entered and secondly, fall in the date range entered to qualify for the unique record count.  I also count each of the individual 11 fields based on the date range using Running Totals.

For testing/development purposes, I created formula looking at the first two fields only.

On one row:  Field1 totals to 14 and field2 totals to 17.  My formula is returning 14 and should be returning at least 17 as field 2 met the criteria 17 times.

I have 3 formulas:

Inserted into group header - reset_patient_total
WhilePrintingRecords;
Global NumberVar patient_total :=0;

Inserted into details - unique_patient_total
WhilePrintingRecords;
If (cdate({database.field1}) >= {?Start_Date} AND cdate({database.field1}) <= {?End_Date}) Then
   Global NumberVar patient_total := patient_total + 1
Else If (cdate({database.field2}) >= {?Start_Date} AND cdate({database.field2}) <= {?End_Date}) Then
   Global NumberVar patient_total := patient_total + 1;

In group footer - display_patient_total
WhilePrintingRecords;
Global NumberVar patient_total;
patient_total

A few notes:  I am using cdate as the data is stored that way in the vendor's database.  There are some rows that count field2 a few times in the total, but mostly it is not counted/considered at all.  These two data points may have data in both fields, at times in one but not the other and lastly in neither.  Seems like Else If statement is ignored most of the time.
0
Comment
Question by:Fifer5280
2 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 37755666
Crystal has trouble with fields with NULL values.  Since NULL is the absence of a value it cannot be compared to anything so if your first date field is NULL, Crystal will terminate evaluation of the formula at that point and return some unknown value.

So here is how to handle that

If (NOT (IsNull({database.field1}))  AND (cdate({database.field1}) >= {?Start_Date} AND cdate({database.field1}) <= {?End_Date}) Then
   Global NumberVar patient_total := patient_total + 1
Else If (NOT (IsNull({database.field12})  (cdate({database.field2}) >= {?Start_Date} AND cdate({database.field2}) <= {?End_Date}) Then
   Global NumberVar patient_total := patient_total + 1;


There is another you can deal with it.  If you have lots of fields some versions of Crystal have a dropdown in the formula editor.  The default is EXCEPTION ON NULL, you can change that to DEFALT ON NULL which will provide a default date/value for evaluation.

Crystal also has a global option to change all NULL fields to default.  It is in the FILE --> REPORT OPTIONS.  This works so long as a NULL field doesn't mean something in the report.  In your case I suspect it would be ok.

mlmcc
0
 

Author Closing Comment

by:Fifer5280
ID: 37757896
It worked.  Thank you for the solution and the info regarding Null fields with Crystal.  Much appreciated.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

963 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