?
Solved

Crystal Reports - Null Values

Posted on 2010-11-09
6
Medium Priority
?
594 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 41

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 101

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 101

Assisted Solution

by:mlmcc
mlmcc earned 500 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 500 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 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