Announcements
 Crystal Reports Forum : General Information : Announcements
Message Icon Topic: If statement in formula Post Reply Post New Topic
Author Message
SATELE01
Newbie
Newbie
Avatar

Joined: 13 Aug 2012
Location: United States
Online Status: Offline
Posts: 24
Quote SATELE01 Replybullet Topic: If statement in formula
    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
IP IP Logged
shanth
Groupie
Groupie


Joined: 06 Aug 2012
Location: United States
Online Status: Offline
Posts: 75
Quote shanth Replybullet 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}
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.

IP IP Logged
SATELE01
Newbie
Newbie
Avatar

Joined: 13 Aug 2012
Location: United States
Online Status: Offline
Posts: 24
Quote SATELE01 Replybullet 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
IP IP Logged
SATELE01
Newbie
Newbie
Avatar

Joined: 13 Aug 2012
Location: United States
Online Status: Offline
Posts: 24
Quote SATELE01 Replybullet 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
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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}
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 27 Aug 2012 at 11:14am
Also I should mention the equals sign is an comparison operator, colon equals (:=) is the assignment operator.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
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.