Solved

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

Posted on 2011-02-23
2
513 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 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 Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 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