Crystal Reports - Null Values

Hi there,

I have a long formula (snippet below) which identifies a series of codes and groups them under various headings.  These codes have a date to that is Blank and some are populated with dates.  I only want those NOT populated with a date or null but am not able to code the formula correctly to verify the results.

if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Audit Committee Chair", "Audit Committee Member"] and {CnCnstncy_1.CnCnstncy_1_Date_To} IsNull then "Audit Committee"

Can someone see what I am doing wrong with the above?  
Thank you -
rporter45Asked:
Who is Participating?
 
deemasConnect With a Mentor Commented:
mlmcc has organized well, but I do think, It would be better, If we would use "Switch-Case" instead of following part
(
if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Audit Committee Chair", "Audit Committee Member"]  then 
    "Audit Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Compensation Advisory Group Chair", "Compensation Advisory Group Member"] then 
   "Compensation Advisory Group" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Deputy Mayor", "Mayor", "Councillor"] then 
   "Dignitaries" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Executive Committee Chair", "Executive Committee Member"]  then 
   "Executive Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Finance Committee Chair", "Finance Committee Member"] then 
    "Finance Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Foundation Board Chair", "Foundation Board Member", "Foundation Board Vice-Chair", "Foundation Board Member at Large", 
"Foundation Board Past Chair", "Foundation Board Secretary", "Foundation Board Treasurer"]  then 
    "Foundation Board"  
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Governance Committee Chair", "Governance Committee Member"] then 
    "Governance Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Hospital Board Chair", "Hospital Board Member"]  then 
    "Hospital Board" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Investment Committee Chair", "Investment Committee Member"] then 
    "Investment Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Nominating Committee Chair", "Nominating Committee Member"]  then 
    "Nominating Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Special Events Committee Chair", "Special Events Committee Member"] then 
    "Special Events Committee" 
)

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
There's a quirk in crystal iisnull must be the first part of your if statement.
0
 
cyberkiwiCommented:
IsNull is a function, so you put something inside the function brackets

if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Audit Committee Chair", "Audit Committee Member"] and IsNull({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Audit Committee"
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
mlmccCommented:
It doesn't need to be first so long as the field being tested isn't used.

mlmcc
0
 
rporter45Author Commented:
Hi there,

I have taken the advice above regarding IsNull but something is still not working.  Attached is the code I am using.  I am trying to verify that the data is working but it's not returning any of the requested names such as the first, Audit Committee.  It also looks as though there are far too many records.

Any suggestions would be appreciated,

Thank you -


// Seletion Committe Prior to null values
if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Audit Committee Chair", "Audit Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Audit Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Compensation Advisory Group Chair", "Compensation Advisory Group Member"] 
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Compensation Advisory Group" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Deputy Mayor", "Mayor", "Councillor"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Dignitaries" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Executive Committee Chair", "Executive Committee Member"] 
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Executive Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Finance Committee Chair", "Finance Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Finance Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Foundation Board Chair", "Foundation Board Member", "Foundation Board Vice-Chair", "Foundation Board Member at Large", 
"Foundation Board Past Chair", "Foundation Board Secretary", "Foundation Board Treasurer"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Foundation Board" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Governance Committee Chair", "Governance Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Governance Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Hospital Board Chair", "Hospital Board Member"] 
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Hospital Board" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Investment Committee Chair", "Investment Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Investment Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Nominating Committee Chair", "Nominating Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Nominating Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Special Events Committee Chair", "Special Events Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Special Events Committee" 

Open in new window

0
 
mlmccConnect With a Mentor Commented:
What do you want returned if ({CnCnstncy_1.CnCnstncy_1_Date_To})  is not null?

Try this one, it is a bit simmpler

mlmcc
If IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then
(
if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Audit Committee Chair", "Audit Committee Member"]  then 
    "Audit Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Compensation Advisory Group Chair", "Compensation Advisory Group Member"] then 
   "Compensation Advisory Group" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Deputy Mayor", "Mayor", "Councillor"] then 
   "Dignitaries" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Executive Committee Chair", "Executive Committee Member"]  then 
   "Executive Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Finance Committee Chair", "Finance Committee Member"] then 
    "Finance Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Foundation Board Chair", "Foundation Board Member", "Foundation Board Vice-Chair", "Foundation Board Member at Large", 
"Foundation Board Past Chair", "Foundation Board Secretary", "Foundation Board Treasurer"]  then 
    "Foundation Board"  
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Governance Committee Chair", "Governance Committee Member"] then 
    "Governance Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Hospital Board Chair", "Hospital Board Member"]  then 
    "Hospital Board" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Investment Committee Chair", "Investment Committee Member"] then 
    "Investment Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Nominating Committee Chair", "Nominating Committee Member"]  then 
    "Nominating Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Special Events Committee Chair", "Special Events Committee Member"] then 
    "Special Events Committee" 
)
Else
     "Date is not null"

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.