Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Multiple If Then Else Statements Post Reply Post New Topic
Author Message
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet Topic: Multiple If Then Else Statements
    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?
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet 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.
IP IP Logged
carstowal
Groupie
Groupie


Joined: 31 Jul 2008
Online Status: Offline
Posts: 80
Quote carstowal Replybullet Posted: 12 Nov 2009 at 7:30am
could it be the response date isn't a NULL value it's blank?
IP IP Logged
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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"
IP IP Logged
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet 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.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
tpreston
Newbie
Newbie


Joined: 26 Jan 2011
Online Status: Offline
Posts: 7
Quote tpreston Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.