Multiple If Then Else Statements
Printed From: Crystal Reports Book — Forum Name: Technical Questions
Posted By: huddy33 — 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?
Posted By: lockwelle — 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 — 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 — 12 Nov 2009 at 7:30am
could it be the response date isn't a NULL value it's blank?
Posted By: huddy33 — 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 — 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 — 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 — 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 — 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 — 2 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.
|
