Not(isnull{mydate}) Conditional Formatting
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15435
Printed Date: 31 Mar 2025 at 12:35am
Topic: Not(isnull{mydate}) Conditional Formatting
Posted By: WhyNot
Subject: Not(isnull{mydate}) Conditional Formatting
Date Posted: 24 Jan 2012 at 6:13am
|
My report has 3 date fields, each of which may be populated from a MYSQL DB, and 3 corresponding text boxes on the report, one for each field. My goal is to use conditional formatting to suppress 2 of the 3 text boxes.
I thought i was on the right track on the first text box with: (isnull({tbl_cmnform.RevisedDate})) and (isnull({tbl_cmnform.RecertificationDate})) in the suppress formula, However, text boxes 2 and 3 do not suppress, and they should as they have no date value. textBox2 suppress:NOT(isnull({tbl_cmnform.RevisedDate})) and (isnull({tbl_cmnform.RecertificationDate})) textbox3 suppress: isnull({tbl_cmnform.RecertificationDate})) seems as though CR does not see the blank date as null? I'm pretty new to CR, I have some basic SQL skills, not much. In a perfect world I would make some kind of If Then statement for the suppress that also varied the text instead of having three text boxes. If (isnull({tbl_cmnform.RevisedDate})) and (isnull({tbl_cmnform.RecertificationDate})) then "Initial" else if NOT(isnull({tbl_cmnform.RevisedDate})) and (isnull({tbl_cmnform.RecertificationDate})) then "Revised" else if (isnull({tbl_cmnform.RecertificationDate})) then "Recertified" end if Probably should get the syntax correct before I attempt the grander things 
|
Replies:
Posted By: kostya1122
Date Posted: 24 Jan 2012 at 12:15pm
|
maybe try (isnull({tbl_cmnform.RevisedDate})or {tbl_cmnform.RevisedDate}="" ) and (isnull({tbl_cmnform.RecertificationDate}) or {tbl_cmnform.RecertificationDate} ="") as for crystal syntax it seems fine axept take out "end if" If (isnull({tbl_cmnform.RevisedDate})) and (isnull({tbl_cmnform.RecertificationDate})) then "Initial" else if NOT(isnull({tbl_cmnform.RevisedDate})) and (isnull({tbl_cmnform.RecertificationDate})) then "Revised" else if (isnull({tbl_cmnform.RecertificationDate})) then "Recertified" |
Posted By: lockwelle
Date Posted: 25 Jan 2012 at 7:13am
|
CR can be odd. if isnull({table.field}) is not returning true, then I would just display {table.field}. If it has a value in it like '1/1/1900' then you will to look for that. If it is blank, and not null, I would expect that the field is coming back as string and not as Date. It is not the default, but if you have checked the option (on Report Options) to convert NULLs to default values, that would also be something that would be causing the formula to not behave as expect HTH
|
Posted By: WhyNot
Date Posted: 25 Jan 2012 at 8:48am
|
Thank you for the response. I get an error like 'expecting a date there', I also tried your formula with
(isnull({tbl_cmnform.RevisedDate})or {tbl_cmnform.RevisedDate}=datetime(0,0,0,0,0,0)) and (isnull({tbl_cmnform.RecertificationDate}) or {tbl_cmnform.RecertificationDate} =datetime(0,0,0,0,0,0)) No date time error, but not working correctly. As near as I can figure as I travel the internet, my problem has to do with the null value there, and my solution seems to be to think of a different way to accomplish my goals. If I do come up with a solution, I will post it. |
Posted By: DBlank
Date Posted: 25 Jan 2012 at 9:19am
|
IMHO, I would test each of your 3 values with 3 isnull formula fields to see if each of those are working as you expected. If they are not it would give you a clue as to where an issue is. isnull({tbl_cmnform.RevisedDate})
isnull({tbl_cmnform.RecertificationDate})
isnull(other date field) Place each next to your dates on each row and see if you get TRUE when the value is empty and and FALSE when the value has any date.
Depending on your report set up, Crystal can treat NULLs a little differently than you might expect but there is usually a pretty straight forward solution.
|