Solved

Crystal Reports - Null Values

Posted on 2010-11-09
6
551 Views
Last Modified: 2012-05-10
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 -
0
Comment
Question by:rporter45
6 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 34096652
There's a quirk in crystal iisnull must be the first part of your if statement.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34096843
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34097122
It doesn't need to be first so long as the field being tested isn't used.

mlmcc
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:rporter45
ID: 34106877
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 125 total points
ID: 34106973
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
 
LVL 1

Accepted Solution

by:
deemas earned 125 total points
ID: 34128180
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now