Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: datediff with current date if a field has null val Post Reply Post New Topic
Author Message
deeps
Newbie
Newbie


Joined: 20 Aug 2013
Online Status: Offline
Posts: 2
Quote deeps Replybullet Topic: datediff with current date if a field has null val
     Posted: 21 Aug 2013 at 6:22pm
I am new to crystal reports, and I have to prepare an ageing report for tickets.
This has to be done on crystal reports 2008. The database is SQL.
There are tickets that are still open, and tickets that are closed.
For closed tickets, the duration should be closed time- open time
And for open tickets, duration should be current time- open time.
 
I did the formula as below:
if IsNull({table.CLOSE_TIME}) Then
datediff('s',{table.OPEN_TIME}, currentdatetime) Else
datediff('s',{table.OPEN_TIME}, {table.CLOSE_TIME});
 
However, this is working only when the close time field has value. For tickets that are open, the duration shows as 0 day 00 h 00 min
 
Kindly request you to please help.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 Aug 2013 at 4:04am

It looks fine. Did you place this formula field on the report to see its result?

are you converting this result into x day xx h xx min because your result will only be one number = to seconds so maybe the 'error' is in that display formula.
 
IP IP Logged
deeps
Newbie
Newbie


Joined: 20 Aug 2013
Online Status: Offline
Posts: 2
Quote deeps Replybullet Posted: 25 Aug 2013 at 9:28pm
Thank you DBlank for your response.
This formula works fine, if I query for tickets which are not closed (which are still open).
However, when I dont give the filter to query for not-closed tickets, this formula is not working.
I kept the formula field on the report also, and it is still the same. It shows 0 days 00 h 00 min
 
I wonder if the report is not able to go through the if else condition, and it is directly going to the else condition.
Could that be a chance?
Please help.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Aug 2013 at 4:24am
place the Close_time field on the report canvas to 'see' what values are appearing for the ones you know to be NULL. perhaps you are converting them to a default value when they are pulled into the report (based on report settings).
You can also insert a test formula to see how the data is being read.
 
IsNull({table.CLOSE_TIME})
 
//NullTest
should display a true for your null rows and a false for your other rows.
 
if it is always false and you are using a default value like 1/1/1900 for the null rows just use that in your formula
 
if {table.CLOSE_TIME}=date(1900,1,1) Then
datediff('s',{table.OPEN_TIME}, currentdatetime) Else
datediff('s',{table.OPEN_TIME}, {table.CLOSE_TIME});
 


Edited by DBlank - 26 Aug 2013 at 4:25am
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.