[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Crystal Report - How to filter Null fields in Select Expert?

Posted on 2009-04-22
Medium Priority
Last Modified: 2012-05-06
My code below works great, but the last line {mwlLoanApp.EscrowNumber} <> "1"  does not include the Null records for this field. How do I get them to include? I've tried to remove the last line and replace it with (isnull{mwlLoanApp.EscrowNumber} or {mwlLoanApp.EscrowNumber} isnull) just to try to return the null records but, crystal will not allow me to save it in Select Expert. I get the following message: "The remaining text does not appear to be part of the formula". How can I get the report to include the null records for {mwlLoanApp.EscrowNumber} with the code below? Thanks.
{mwlAppraiserNote.UpdatedOnDate} = currentdate and
{mwlLoanData.FinancingType} <> "F" and
{mwlLoanData.FinancingType} <> "V" and
{mwlLoanApp.EscrowNumber} <> "1"

Open in new window

Question by:asmyatt
  • 2
LVL 13

Expert Comment

ID: 24204925
write a new formula to modify the EscrowNumber field to include some values other than 1 in place of null:


if isnull({mwlLoanApp.EscrowNumber})= true or {mwlLoanApp.EscrowNumber}= ''
then 1000

use the above formula instead of the actual field in the last line of your original formula:

{mwlAppraiserNote.UpdatedOnDate} = currentdate and
{mwlLoanData.FinancingType} <> "F" and
{mwlLoanData.FinancingType} <> "V" and
{@Escrow_no} <> "1"


LVL 26

Accepted Solution

Kurt Reinhardt earned 2000 total points
ID: 24205271
The problem with using a formula is that it won't be processed on the database, which means you'll return all escrow numbers and then filter them out after the fact.  On top of that, NULL values need to be evaluated first in Crystal Reports. I would recommend that you modify the record selection criteria in the formula editor rather than using either a formula or the Select Expert.

To access the Selection Criteria, navigate to Report|Selection Formulas|Record... (or something similar, depending on your version) and modify the code.

(IsNull({mwlLoanApp.EscrowNumber}) or {mwlLoanApp.EscrowNumber} <> "1")
And {mwlAppraiserNote.UpdatedOnDate} = CurrentDate
And {mwlLoanData.FinancingType} <> "F"
And {mwlLoanData.FinancingType} <> "V"

Open in new window

LVL 13

Expert Comment

ID: 24205355
Agree using the record selection formula is more effecient!

Expert Comment

ID: 24863544
Some CRW composers prefer not to deal with NULL at all. Depending on the target database(s) default settings, you may be able to simplify things a tad by going to File, Options, Reporting tab, and evaluate the use of the checkbox labeled "Convert NULL Field Value to Default". I have not used this feature in a few years, when NULL handling was more difficult (or seemed that way) and have cited the location in V8.5 and 10, although it's bound to be in 11 as well.

Since this is likely a "when reading records" conversion it could make record selection more straightforward.

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.

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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