Including 2 NULL Fields in Selection Criteria

[OldGrayMare]

I use Crystal v11. I want to see all records where either the product name or the closure product name are blank and the records also meet the other criteria.

When I use the selection criteria below, I get all records, even those whose products are not null.


IsNull ({HPD_Help_Desk.Product Name}) or
IsNull ({HPD_Help_Desk.Closure Product Name}) or
{HPD_Help_Desk.Submit Date} in LastFullMonth and
{HPD_Help_Desk.Status} > "2" and
{HPD_Help_Desk.Assignee} <> "Spectrum Spectrum"

I have also tried criteria like:

(
IsNull ({HPD_Help_Desk.Product Name}) or
IsNull ({HPD_Help_Desk.Closure Product Name}) or
{HPD_Help_Desk.Submit Date} in LastFullMonth
)
and
{HPD_Help_Desk.Status} > "2" and
{HPD_Help_Desk.Assignee} <> "Spectrum Spectrum"

If I change the first "and" to an "or" I get no records at all.

I'd appreciate your assistance.

Thank you,

Rachel

 

[Turkbear]

Hi,
Try testing for blanks and NULLs ( not the same in some databases) - :

(
(IsNull ({HPD_Help_Desk.Product Name}) or Trim({HPD_Help_Desk.Product Name}) = "")
OR
IsNull ({HPD_Help_Desk.Closure Product Name}) or Trim({HPD_Help_Desk.Closure Product Name}) = "")
)  [COLOR=green]//The above gets only NULL or Blank records then 
// then,the section below adds further criteria for these records [/color]
and
(
{HPD_Help_Desk.Submit Date} in LastFullMonth
and
{HPD_Help_Desk.Status} > "2" 
and
{HPD_Help_Desk.Assignee} <> "Spectrum Spectrum"
)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[OldGrayMare]

I tried the suggested formula but get an error:
"The remaining text does not appear to be part of the formula."

This is the selection criteria I pasted that is giving the error:

) //The above gets only NULL or Blank records then
// then,the section below adds further criteria for these records
and
(
{HPD_Help_Desk.Submit Date} in LastFullMonth
and
{HPD_Help_Desk.Status} > "2"
and
{HPD_Help_Desk.Assignee} <> "Spectrum Spectrum"
)

 

[lbass]

(
(
IsNull ({HPD_Help_Desk.Product Name}) or Trim({HPD_Help_Desk.Product Name}) = ""
)
OR
[red]( //Add this paren[/red]
IsNull ({HPD_Help_Desk.Closure Product Name}) or Trim({HPD_Help_Desk.Closure Product Name}) = ""
)
) //The above gets only NULL or Blank records then
// then,the section below adds further criteria for these records
and
(
{HPD_Help_Desk.Submit Date} in LastFullMonth
and
{HPD_Help_Desk.Status} > "2"
and
{HPD_Help_Desk.Assignee} <> "Spectrum Spectrum"
)

-LB

 

[OldGrayMare]

THANK YOU, -LB!!! This works! Do you know of any white papers that explain in laymans terms about parenthesis use rules in Crystal? I just don't understand how to use parentheses.

 

[lbass]

One basic rule is that there has to be an closed paren for every open paren. Other than that, the use of parens is driven by what goes together--basically you add in parens if the statement is unclear without them, e.g., if you cannot tell which clauses go with which in an 'or' statement. In the following:

{table.field1} = "A" or
{table.field2} = "B" and
{table.field3} = "C"

You need to tell the report whether C must always be true:

(
{table.field1} = "A" or
{table.field2} = "B"
) and
{table.field3} = "C"

...or whether C must be true only when B is true:

{table.field1} = "A" or
(
{table.field2} = "B" and
{table.field3} = "C"
)

A final comment--more parens are not necessarily better--just make sure the necessary ones are there.

I don't know of any resources in particular that explain this--I think it is more an issue of understanding the way in which a statement is logically processed. As another example, in the following, the last clause if true will return a record even if the previous clauses are not true:

{table.field1} = "A" and
{table.field2} = "B" or
{table.field3} = "C"

So if you mean:

{table.field1} = "A" and
(
{table.field2} = "B" or
{table.field3} = "C"
)

...you have to explicitly say so by adding parens. So you just need to ask yourself what you mean by the statement and then check to see if it could be interpreted any other way, and if so, add the appropriate parens.

-LB

 

[Turkbear]

Hi,
Thanks for the catch LB, I should have done a better job of counting
( I usually use the + - method - for every ( add 1 and for every ) subtract 1, if the count does not end up as 0 then something is mismatched)







To Paraphrase:"The Help you get is proportional to the Help you give.."