Print Page | Close Window

Highlighting NULL values

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=10323
Printed Date: 27 Apr 2024 at 7:03pm


Topic: Highlighting NULL values
Posted By: jgarner
Subject: Highlighting NULL values
Date Posted: 22 Jun 2010 at 4:15am
I have a date field where I would like to highlight NULL values. The Highlighting Expert doesn't have this option. Is there another way to do this?


Replies:
Posted By: kevlray
Date Posted: 22 Jun 2010 at 4:31am

In the 'Font' and 'Border' tabs of the 'Format Editor', there is a place to enter a formula for the color (among other things).  You can create a formula that checks for a null value.

 
I hope this helps.

Posted By: jgarner
Date Posted: 22 Jun 2010 at 4:49am
any suggestions on what to write for the formula?

Posted By: DBlank
Date Posted: 22 Jun 2010 at 5:27am
You cannot highlight NULL records as there is nothing to highlight. You will need to insert a value using a formula and then conditionally highlight it. Easisest way is to convert it to a string and then highlight using the string value you insert for NULLS. Here is a sample.
if isnull(table.date) then 'Missing' else totext(table.date,'MM/dd/yyyy')

Posted By: jgarner
Date Posted: 22 Jun 2010 at 6:06am
Thanks for the suggestions. I'm still having a problem with the formula though. I was getting error messages with this formula:
 
if isnull(tblGrantRevisit.SubmittedToPandC) then 'Missing' else totext(tblGrantRevisit.SubmittedToPandC,'MM/dd/yyyy')
 
Until I changed it to this formula:
 
if isnull ({tblGrantRevisit.SubmittedToPandC}) then 'Missing' else totext ({tblGrantRevisit.SubmittedToPandC},'MM/dd/yyyy')
 
Now, instead of the NULL values showing 'Missing', there's a date displayed as 12/31/-4714.
 
Not sure how it's coming up with that date?

Posted By: DBlank
Date Posted: 22 Jun 2010 at 6:10am
Yes you need the brackets around the fields...
 
Do you have it set to insert default values for NULLs (either at the report options level or the formula level)?

Posted By: jgarner
Date Posted: 22 Jun 2010 at 6:15am
That was it! Once I unchecked those boxes, it worked as you suggested. Thanks again.


Print Page | Close Window