We help IT Professionals succeed at work.

Crystal Reports - Null Values

614 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 -
Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
There's a quirk in crystal iisnull must be the first part of your if statement.
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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"
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc

Author

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

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.