Null Values - How do you extract

[shaleen7]

How do you extract the NULL VALUES
EXAMPLE TABLE

NAME COMPANY
JAMES
PAUL COMPANY1
JANE COMPANY2
KERRI COMPANY4

I just want to pull the names of the people were the company field is blank.

I went to the Select Expert and tried several expressions and nothing worked.
IS EQUAL TO 0
USING THE FORMULA : ISNULL({COMPANY})
USING THE FORMULA : COMPANY = ""

Any suggestions? Many thanks

 

[pgtek]

hi
Just click on the select expert and select the field you need
and filter
select the show formula
and type this
isnull({your_field_name})


cheers

pgtek

 

[resdevlabs]

IsNull( {Company} ) should work. What database are you using? Is there anything else in the Select Expert? Are you joining multiple tables?

Rob

 

[pgtek]

hi also Try this one

hi
Just click on the select expert and select the field you need
and filter
select the show formula
and type this
(isnull({your_field_name}) or {your_field_name = " &quot


cheers

pgtek

 

[Madawc]

Crystal's default is to produce nothing when it finds a null. When testing, you always have to check for a null before checking for other values, as in the formulas given above.

If you're new to Crystal, you also need to be watching out for Division By Zero. In this case, the language allows you to divide by a value that may be zero, and then stops the report if such a value is found.

Madawc Williams
East Anglia, Great Britain

 

[lbass]

You could try:

isnull({table.company}) or
trim({table.company}) = ""

-LB

 

[shaleen7]

Yes multiple tables have been joined.

sample of the selection expert
{VRPT_TABING.INIING} in ["DES", "FAD", "HDF", "JJK", "RBL", "SSH"] and
{VRPT_TABTYP.CODTYP} <> &quot;PSP&quot; and
{VTABBRE.DATDEP} in DateTime (2003, 01, 01, 0, 0, 0) to DateTime (2003, 12, 31, 0, 0, 0) and
{VRPT_TABPAY.CODPAY} = &quot;US&quot; and
isnull({VRPT_TABCOM.COMPANY})

 

[lbass]

As a general rule, &quot;isnull&quot; statements should be first in a formula. You could try the following, making sure to put the first two lines within parentheses:

(isnull({VRPT_TABCOM.COMPANY}) or
trim({VRPT_TABCOM.COMPANY}) = &quot;&quot and
{VRPT_TABING.INIING} in [&quot;DES&quot;, &quot;FAD&quot;, &quot;HDF&quot;, &quot;JJK&quot;, &quot;RBL&quot;, &quot;SSH&quot;] and
{VRPT_TABTYP.CODTYP} <> &quot;PSP&quot; and
{VTABBRE.DATDEP} in DateTime (2003, 01, 01, 0, 0, 0) to DateTime (2003, 12, 31, 0, 0, 0) and
{VRPT_TABPAY.CODPAY} = &quot;US&quot;

If this doesn't work, you should provide info about your joins--what kinds of joins from what tables to what tables.

-LB

 

[shaleen7]

Placing the null statement in the front of the formula worked. Thanks so much!