?
Solved

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

Posted on 2011-02-23
2
Medium Priority
?
532 Views
Last Modified: 2012-05-11
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?

Thanks!
0
Comment
Question by:robthomas09
2 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 34965863
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
    {payer_mstr.payer_name}
else If {@RefType} = 'I' and left({transactions.tracking_desc_40},1) = 'D' then
   'DMERC Region B'
else
   ''

mlmcc
0
 

Author Closing Comment

by:robthomas09
ID: 34966477
Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

850 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