Solved

# Crystal Reports XI Formula - Else If Statement Ignored

Posted on 2012-03-22
911 Views
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
Question by:Fifer5280
[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

LVL 100

Accepted Solution

mlmcc earned 500 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

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

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Crystal Report - Charts 4 53
SSMS Imprt data from Excel 7 38
Join Number Parameter with commas 8 37
What is GIS method of Geometry data type? 6 36
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
###### Suggested Courses
Course of the Month3 days, 23 hours left to enroll