Crystal Reports v14 not finding all null values

Question

Alright, I've run into this before and have gotten everything to work correctly in the past. I have an SQL code that was created that now needs to be turned into a crystal report. The SQL shows 956 lines, but Crystal is only showing 886.
Here is the SQL code:

SELECT 
I4240,
I4201,
I4202,
I4203,
I4204,
I4206,
I4213,
I4214,
I4225,
I4208,
I4299 
FROM 
    MT.INVENTORY
WHERE
(
    I4202 IN ('UNKNONWN','VERIFY MFR','OTHER','VARIOUS','TBD','NA','N/A') OR
    (
        I4203 IN ('UNKNONWN','VERIFY MODEL NUMBER','OTHER','VARIOUS','TBD','NA','N/A','MISCELLANEOUS') 
        OR I4203 IS NULL 
        OR LENGTH(I4203)=0
    ) OR
    (
        I4204 IN ('UNKNOWN DESCRIPTION','VERIFY DESCRIPTION','OTHER','VARIOUS','TBD','NONE - NO STD USED','NA','N/A','MISCELLANEOUS') 
        OR I4204 IS NULL 
        OR LENGTH(I4204)=0
    ) 
) AND
I4240 NOT IN ('MT','STD','NESD')

ORDER BY I4240,I4202,I4203,I4204

and the record selection formula from CR:

(
    {Inventory.I4240} <> 'mt' and
    {Inventory.I4240} <> 'std' and
    {Inventory.I4240} <> 'nesd'
) 
AND
(
    (
        {Inventory.I4202} = 'UNKNONWN' OR
        {Inventory.I4202} = 'VERIFY MFR' OR
        {Inventory.I4202} = 'OTHER' OR
        {Inventory.I4202} = 'VARIOUS' OR
        {Inventory.I4202} = 'TBD' OR
        {Inventory.I4202} = 'NA' OR
        {Inventory.I4202} = 'N/A'
    )
    OR
    (
        {Inventory.I4203} = 'UNKNONWN' OR
        {Inventory.I4203} = 'VERIFY MODEL NUMBER' OR
        {Inventory.I4203} = 'OTHER' OR
        {Inventory.I4203} = 'VARIOUS' OR
        {Inventory.I4203} = 'TBD' OR
        {Inventory.I4203} = 'NA' OR
        {Inventory.I4203} = 'N/A' OR
        {Inventory.I4203} = 'MISCELLANEOUS' OR
        ISNULL({Inventory.I4203}) OR
        LENGTH(trim({Inventory.I4203})) < 1 OR
        INSTR(trim({Inventory.I4203}), "") = 0 OR
        TRIM({Inventory.I4203}) = ""
    )
    OR
    (
        {Inventory.I4204} = 'UNKNOWN DESCRIPTION' OR
        {Inventory.I4204} = 'VERIFY DESCRIPTION' OR
        {Inventory.I4204} = 'OTHER' OR
        {Inventory.I4204} = 'VARIOUS' OR
        {Inventory.I4204} = 'TBD' OR
        {Inventory.I4204} = 'NONE - NO STD USED' OR
        {Inventory.I4204} = 'NA' OR
        {Inventory.I4204} = 'N/A' OR
        {Inventory.I4204} = 'MISCELLANEOUS' OR
        ISNULL({Inventory.I4204}) OR
        LENGTH(trim({Inventory.I4204})) < 1 OR
        INSTR({Inventory.I4204}, "") = 0 OR
        TRIM({Inventory.I4204}) = ""
    )
)

Any help would be appreciated.

Ryan Ryan · Accepted Answer · 2014-03-04T21:36:12

In Crystal, if a particular field can be null then you need to check for that condition as the very first thing you do with it, otherwise the entire formula will error out and nothing will be evaluated.

So in your case, fields I4203 and I4204 need the isnull() check moved to the top of their respective sections at the very least. If I4240 and I4202 can be null, then you should handle those conditions as well.

Also, you have a couple references to the word "UNKNONWN"; is that a typo?

Crystal Reports v14 not finding all null values

2 Answers