Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Null 'Date' Value Post Reply Post New Topic
Author Message
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Topic: Null 'Date' Value
    Posted: 20 Nov 2009 at 12:40pm

Crystal Report XI

If a date field Is Null on a CR, is there a formula that I could have it display the word 'Various' instead?
 
Thanks for your help.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Nov 2009 at 1:17pm
Change the red part to how you want your date formatted:
if isnull(date) then 'Various' else totext(date,'MM/dd/yyyy')


Edited by DBlank - 20 Nov 2009 at 1:18pm
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 20 Nov 2009 at 1:25pm
I used this formula based on your suggestion:
 
if isnull({tblWilliamsGrantExpenditures.Date}) then 'Various' else totext({tblWilliamsGrantExpenditures.Date},'MM/dd/yyyy')
 
but getting this in the Null values: 12/31/-4714. All of the other dates are correct that had values. Am I missing something?
 
Thanks for the help
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Nov 2009 at 1:28pm
When you palce the {tblWilliamsGrantExpenditures.Date} field on your report canvas, what appears for the NULL values?
is it blank or was there a defualt value inserted?
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 20 Nov 2009 at 1:39pm
The NULL values are blank on the report for the {tblWilliamsGrantExpenditures.Date}
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 20 Nov 2009 at 1:42pm
THese fields are also Null in the Access table that I'm pulling the data from.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Nov 2009 at 1:44pm
Weird. Never had a problem with that formula.
I am guessing as I have not seen that before but try:
 
if isnull({tblWilliamsGrantExpenditures.Date}) or {tblWilliamsGrantExpenditures.Date}<Date(1900,01,01) then 'Various' else totext({tblWilliamsGrantExpenditures.Date},'MM/dd/yyyy')
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 20 Nov 2009 at 1:52pm

That did the trick! Now that I see the formula, I can understand how it works. As for me trying to come up with this formula by myself, there is not enough time in the week.......Thanks for your efforts.

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.031 seconds.