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

Posted on 2009-04-22
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
    LVL 13

    Expert Comment

    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

    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

    Agree using the record selection formula is more effecient!

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    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 …
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now