Print Page | Close Window

Formula for Text if time and date field is blank

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11700
Printed Date: 04 May 2024 at 11:16pm


Topic: Formula for Text if time and date field is blank
Posted By: Deadstroke
Subject: Formula for Text if time and date field is blank
Date Posted: 17 Nov 2010 at 9:39am

I have a time and date field on my report. If that has not been filled out or the request not completed it is blank when run my report.

Instead of leaving a blank field on my report I would like to have a set text entered instead.
 
 
What would be the best way to go about this?


Replies:
Posted By: DBlank
Date Posted: 17 Nov 2010 at 10:06am
create a formula to convert to what you want.
if isnull(datetimefield) then 'Your text here' else
totext(datetimefield,format)

Posted By: Deadstroke
Date Posted: 18 Nov 2010 at 2:44am
if isnull({Problem.NextUpdate}) then 'Monitoring' else
totext({Problem.NextUpdate},"")
 
ok i tried to enter in format but keep getting errors so I entered a blank string to get through and still cant get the test to display.
 
if isnull({Problem.NextUpdate}) then 'Monitoring' else
totext({Problem.NextUpdate},"MM/dd/yyyy")
 
Tried this and got no errors..  But still no text

Posted By: DBlank
Date Posted: 18 Nov 2010 at 3:48am
are your nulls using a default value like 01/01/1900?

Posted By: DBlank
Date Posted: 18 Nov 2010 at 3:49am
are you using the formula field for displaying?

Posted By: Deadstroke
Date Posted: 18 Nov 2010 at 6:52am
just asked our admin and null values are set to null and I am in the format editor, display string is where I am putting the formula

Posted By: DBlank
Date Posted: 18 Nov 2010 at 7:14am
i have had problems with the display string option, especially handling NULLS.
Instead of worring about that create a new formula field to use in its place.

Posted By: Deadstroke
Date Posted: 18 Nov 2010 at 8:48am
Ok created a new formula and that worked.. Thank you very much..
 
So I thought I would take that to other fields so I created a new formula
 
if isnull({Problem.ResolutionDuration}) then 'Monitoring' else
totext({Problem.ResolutionDuration} \ 60, 0, '') + ' hour(s), ' + totext({Problem.ResolutionDuration} mod 60, 0, '') + ' minutes'
But instead of displaying text it comes up with neg amount of hours and mins.
 
Update: Just found out there is always a value in it so if less then 0 it is surpressed. So thats explains why the null vaule doesnt work..

Posted By: Deadstroke
Date Posted: 18 Nov 2010 at 9:08am
Got it the right formula is:
 
if ({Problem.ResolutionDuration})<1 then 'Monitoring' else
totext({Problem.ResolutionDuration} \ 60, 0, '') + ' hour(s), ' + totext({Problem.ResolutionDuration} mod 60, 0, '') + ' minutes'


Print Page | Close Window