Crystal Reports Formula for Text if time and date field is blank

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions

Topic: Formula for Text if time and date field is blank

Author	Message
Deadstroke Newbie Joined: 17 Nov 2010 Online Status: Offline Posts: 5	Topic: Formula for Text if time and date field is blank 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?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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)
	IP Logged

Deadstroke Newbie Joined: 17 Nov 2010 Online Status: Offline Posts: 5	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 Edited by Deadstroke - 18 Nov 2010 at 2:55am
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 18 Nov 2010 at 3:48am
	are your nulls using a default value like 01/01/1900?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 18 Nov 2010 at 3:49am
	are you using the formula field for displaying?
	IP Logged

Deadstroke Newbie Joined: 17 Nov 2010 Online Status: Offline Posts: 5	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 Edited by Deadstroke - 18 Nov 2010 at 7:12am
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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.
	IP Logged

Deadstroke Newbie Joined: 17 Nov 2010 Online Status: Offline Posts: 5	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.. Edited by Deadstroke - 18 Nov 2010 at 8:54am
	IP Logged

Deadstroke Newbie Joined: 17 Nov 2010 Online Status: Offline Posts: 5	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'
	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.031 seconds.