• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

Crystal Reports 11 - if-then syntax question SQL Server 2005

Hello experts,

In a formula field, I have t he below syntax:

If {@RefType} = 'P' then {@Guarantor Name} else
If {@RefType} = 'I' and left({transactions.tracking_desc_40},1) = 'D' then 'DMERC Region B' else
If {@RefType} = 'I' and (left({transactions.tracking_desc_40},1) <> 'D' or {transactions.tracking_desc_40} = '' or isNull({transactions.tracking_desc_40})) then {payer_mstr.payer_name} else

This seems to function accurately when {transactions.tracking_desc_40}  starts with 'D', but when it's blank or null, it doesnt print payer_mstr.payer_name column, and we have verified that there is indeed a value in the payer_mstr.payer_name column as we have unhid it directly on the report.

Is there a problem with the way we have structured the nested if statements?

1 Solution
You have to test the NULL condition first.  Any test on  a NULL value except IsNull terminates the formula with undefined result.  Try it this way

If {@RefType} = 'P' then
     {@Guarantor Name}
else If {@RefType} = 'I' and ( isNull({transactions.tracking_desc_40}) or
                                                       {transactions.tracking_desc_40} = '' or
                                                  left({transactions.tracking_desc_40},1) <> 'D') then
else If {@RefType} = 'I' and left({transactions.tracking_desc_40},1) = 'D' then
   'DMERC Region B'

robthomas09Author Commented:
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now