Print Page | Close Window

If statement in formula

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Announcements
Forum Discription: Please check this section for the latest announcements from Crystal Reports Forum
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17424
Printed Date: 29 Apr 2024 at 12:01am


Topic: If statement in formula
Posted By: SATELE01
Subject: If statement in formula
Date Posted: 27 Aug 2012 at 9:41am

In an SQL database I have a field called Termination Date that is populated with 9999-12-31 for employees that have not been terminated. I'm trying to create a formula that would put a blank in the crystal report field if the termination date is 9999-12-31 otherwise I would use the termination date.

 
I have tried this formula but I keep getting an error that a date is required
 
if {dt_Employee_Client.Termination_Date} = 9999-12-31
then {dt_Employee_Client.Termination_Date} = '  '
else {dt_Employee_Client.Termination_Date}
 
Any help would be appreciated


Replies:
Posted By: shanth
Date Posted: 27 Aug 2012 at 10:07am
Try this one
if totext({dt_Employee_Client.Termination_Date}) = '9999-12-31'
then totext({dt_Employee_Client.Termination_Date}) = '  '
else {dt_Employee_Client.Termination_Date}= {dt_Employee_Client.Termination_Date}

Posted By: kevlray
Date Posted: 27 Aug 2012 at 10:08am
A couple of things that I noticed. 9999-12-31 is not a date (in fact I think CR would translate it as a expression).  I am not sure that if you used the date function that is would translate it properly.  Two you cannot assign value to a field, but you might be able to do the following.

In an SQL database I have a field called Termination Date that is populated with 9999-12-31 for employees that have not been terminated. I'm trying to create a formula that would put a blank in the crystal report field if the termination date is 9999-12-31 otherwise I would use the termination date.

 
I have tried this formula but I keep getting an error that a date is required
 
if totext({dt_Employee_Client.Termination_Date},"yyyy-mm-dd" = "9999-12-31"
then  '  '
else {dt_Employee_Client.Termination_Date}

I am not sure if the formatting for the totext is correct.

Posted By: SATELE01
Date Posted: 27 Aug 2012 at 10:35am
if totext({dt_Employee_Client.Termination_Date}) = '9999-12-31'
then totext({dt_Employee_Client.Termination_Date}) = '  '
else {dt_Employee_Client.Termination_Date}= {dt_Employee_Client.Termination_Date}
 
tHIS IS JUST RETURNING A  "TRUE" RESPONSE. no dates or blanks

Posted By: SATELE01
Date Posted: 27 Aug 2012 at 10:38am
if totext({dt_Employee_Client.Termination_Date},"yyyy-mm-dd" = "9999-12-31"
then  '  '
else {dt_Employee_Client.Termination_Date}
 
error message a ) is missing

Posted By: kevlray
Date Posted: 27 Aug 2012 at 11:12am
if totext({dt_Employee_Client.Termination_Date},"yyyy-mm-dd") = "9999-12-31"
then  '  '
else {dt_Employee_Client.Termination_Date}

Posted By: kevlray
Date Posted: 27 Aug 2012 at 11:14am
Also I should mention the equals sign is an comparison operator, colon equals (:=) is the assignment operator.

Posted By: hilfy
Date Posted: 28 Aug 2012 at 3:24am
Also, if Termination_date is a date field, you'll get a message like "A string is required here" with this formula because the results of both the Then and the Else must be of the same type.  I would do something like this:
 
if {dt_Employee_Client.Termination_Date} <> Date(9999, 12, 31) then {dt_Employee_Client.Termination_Date}
 
This should return null (blank) when the date is 12/31/9999 and the date value if it is not.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window