Print Page | Close Window

Multiple If Then Else Statements

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8312
Printed Date: 29 Apr 2024 at 9:19pm


Topic: Multiple If Then Else Statements
Posted By: huddy33
Subject: Multiple If Then Else Statements
Date Posted: 12 Nov 2009 at 6:01am
I am trying to write a formula field to return a value based on the following syntax:

If ({Sourcing_Hotel_Request.HotelIsNotAvailable}=True)
then "Hotel Not Available"
Else If (IsNull({Sourcing_Hotel_Request.ResponseDate}))
then "Response Not Yet Received"
Else If (Not IsNull({Sourcing_Hotel_Request.ResponseDate}))
then "Response Received"

I am getting "Response Received" even when the the {Sourcing_Hotel_Request.ResponseDate} is Null.  What am I missing?


Replies:
Posted By: lockwelle
Date Posted: 12 Nov 2009 at 6:18am
it would seem correct, this is really the same, just with fewer () and formatted differently...
If {Sourcing_Hotel_Request.HotelIsNotAvailable}=True then
 "Hotel Not Available"
Else
 If IsNull({Sourcing_Hotel_Request.ResponseDate}) then
  "Response Not Yet Received"
 Else 
   "Response Received"
 
HTH

Posted By: huddy33
Date Posted: 12 Nov 2009 at 6:27am
Thanks, Lockwelle.  I am still receiving "Response Received" for those who meet one of the statements, namely statement #2.  It's like it's skipping over it.

Posted By: carstowal
Date Posted: 12 Nov 2009 at 7:30am
could it be the response date isn't a NULL value it's blank?

Posted By: huddy33
Date Posted: 12 Nov 2009 at 10:19am
Originally posted by carstowal

could it be the response date isn't a NULL value it's blank?


When I look in the table to see the actual stored data, it says NULL.  If I try to make it  that field search for '', it gives me the message that it has to be date/time.

Posted By: DBlank
Date Posted: 12 Nov 2009 at 10:41am

Try and rework it to deal with NULLS first. I have problems in IF -THENs if the NULL is not the first condition then it seems to ignore that part of the process. I think this is still logically correct but not sure so you may need to rework it a little...

If {Sourcing_Hotel_Request.HotelIsNotAvailable}=False
and IsNull({Sourcing_Hotel_Request.ResponseDate})
then "Response Not Yet Received"
else
If {Sourcing_Hotel_Request.HotelIsNotAvailable}=True then 
"Hotel Not Available" Else "Response Received"

Posted By: huddy33
Date Posted: 12 Nov 2009 at 11:00am
Is there anything else in the report that I should check?  This still is returning a value of "Response Received" even if they meet the criteria in the other clauses.

Posted By: lockwelle
Date Posted: 12 Nov 2009 at 11:13am
last idea, because I always forget DBlank mentioning of checking for isnull first, but in your report, File/Options/Reporting, are either of the convert Null to default checked?  That would change the value in the DB from NULL to some other value in the report. 
 
The other way to debug this, display the value of the field on the report AND the value from the formula at the same time and compare results.  Then you can try changing to the formula to check for "" and see if it works.
 
HTH

Posted By: DBlank
Date Posted: 13 Nov 2009 at 8:21am
Sorry I was rushing out the door on my last post and the ISNULL had to be the first part of the first  condition...
If IsNull({Sourcing_Hotel_Request.ResponseDate}) and
{Sourcing_Hotel_Request.HotelIsNotAvailable}=False
then "Response Not Yet Received"
else
If {Sourcing_Hotel_Request.HotelIsNotAvailable}=True then 
"Hotel Not Available" Else "Response Received"
 
Also break this down into parts and test each one out to see if you can tell where it is breaking. Just do one condition at a time to see if you can get them to work that way.
Place all of your condition fields on the row and make sure your logic was correct. It is easy to flip these conditions. Keep in mind Lockwelles notation about the "Exceptions for Nulls" or "Default for Nulls" and how thaty can change your formula.

Posted By: tpreston
Date Posted: 02 Jun 2016 at 6:46am
Originally posted by DBlank

I have problems in IF -THENs if the NULL is not the first condition then it seems to ignore that part of the process.



Yep, found that out months ago. It was very frustrating until I stumbled upon the solution via trial & error.


Print Page | Close Window