Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Totext and Dates Post Reply Post New Topic
Author Message
bremen
Groupie
Groupie
Avatar

Joined: 28 May 2013
Online Status: Offline
Posts: 93
Quote bremen Replybullet Topic: Totext and Dates
    Posted: 27 Feb 2017 at 2:32am
I am working with two date time fields.  My current formula is

if
isnull ({Table.Datefield1}) then
{Table.Datefield2}else
 totext ({Table.Datefield1} , 'dd-MMM-yy')

{Table.Datefield1} and {Table.Datefield2} are Date/time fields and I would like them formatted as dd-MMM-yy.


I tired to change {Table.Datefield2} to text as well, but I get an error saying too many arguments.


IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2017 at 2:54am
a couple of ways to approach this but in any case the output of the formula has to always be the same data type

if isnull ({Table.Date1}) then
totext({Table.Date2},'dd-MM-yy') else
totext ({Table.Date1} , 'dd-MMM-yy')

Or make the output always a date field and then use formatting on that


if isnull ({Table.Date1}) then
{Table.Date2} else {Table.Date1}
Right click the resulting formula field on report canvas and select format field option
Use the "Date and Time" tab to customize it to your desired visual output

Option 2 is usually better if you need to still do grouping or sorting on the result as it keeps the data as a datetime field and you can use the sorts and groups that are unique to that data type.

Edited by DBlank - 27 Feb 2017 at 2:55am
IP IP Logged
bremen
Groupie
Groupie
Avatar

Joined: 28 May 2013
Online Status: Offline
Posts: 93
Quote bremen Replybullet Posted: 27 Feb 2017 at 3:10am
I tried both of your formulas witch no luck.  Apparently what I thought was a Date Time is actually a string.....

Table.Date1 = DateTime
Table.Date2 = String
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2017 at 3:12am
what is the format of the string and is it consistent?
IP IP Logged
bremen
Groupie
Groupie
Avatar

Joined: 28 May 2013
Online Status: Offline
Posts: 93
Quote bremen Replybullet Posted: 27 Feb 2017 at 3:16am
01/24/2016.02:20:16

Yes it is consistent thorough out


Edited by bremen - 27 Feb 2017 at 3:17am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2017 at 3:19am
if isnull ({Table.Date1}) then
DATE({Table.Date2}) else {Table.Date1}

Use the format option on the resulting field

Edited by DBlank - 27 Feb 2017 at 3:20am
IP IP Logged
bremen
Groupie
Groupie
Avatar

Joined: 28 May 2013
Online Status: Offline
Posts: 93
Quote bremen Replybullet Posted: 27 Feb 2017 at 4:08am
I Input the formula and I don't get and error when I check for errors or save and close, but when the formula editor window closes I get an error "Bad Date format string"

Edited by bremen - 27 Feb 2017 at 4:08am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2017 at 6:20am
Date2 has non datevalues in it.
How do you want to handle those?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2017 at 10:44am
You can use an isdate() condition to exclude the bad formatted dates and prevent the error but you still would need to know how you want the errors handled.
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.012 seconds.