Print Page | Close Window

Null 'Date' Value

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=8428
Printed Date: 27 Apr 2024 at 11:20am


Topic: Null 'Date' Value
Posted By: jgarner
Subject: Null 'Date' Value
Date 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.


Replies:
Posted By: DBlank
Date 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')

Posted By: jgarner
Date 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

Posted By: DBlank
Date 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?

Posted By: jgarner
Date Posted: 20 Nov 2009 at 1:39pm
The NULL values are blank on the report for the {tblWilliamsGrantExpenditures.Date}

Posted By: jgarner
Date Posted: 20 Nov 2009 at 1:42pm
THese fields are also Null in the Access table that I'm pulling the data from.

Posted By: DBlank
Date 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')

Posted By: jgarner
Date 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.


Print Page | Close Window