Select and Exclude Field Values with NULL

[2ffat]

I'm using CR 8.5SP3 and SQL server. We have several reports where the user selects starting and ending values for a field and then selects exclusion values for the same field. The report selection formula looks like:

...
{PurchasedLogs.CurDeliveryPad} = {?Select Pad} and
{PurchasedLogs.CurDeliveryPad} <> {?Exclude Pad} and
...

So far so good. The reports work well. Now we have a situation where the the field must also be checked for a NULL. I've tried

...
(Len({PurchasedLogs.CurDeliveryPad}) = 0 or {PurchasedLogs.CurDeliveryPad} = {?Select Pad}) and
{PurchasedLogs.CurDeliveryPad} <> {?Exclude Pad} and
...

but the rows NULL are not showing up.

Am I missing something or is this even possible?

James P. Cottingham

^{There's no place like 127.0.0.1.
There's no place like 127.0.0.1.}

 

[lyanch]

(isnull({PurchasedLogs.CurDeliveryPad}) or
{PurchasedLogs.CurDeliveryPad} = {?Select Pad})

Lisa

 

[2ffat]

Thanks, but the results are the same. BTW, I've also tried turning off "Converting NULLs to default" with no luck.

James P. Cottingham

^{There's no place like 127.0.0.1.
There's no place like 127.0.0.1.}

 

[lyanch]

Are you sure the rows are null? Are they empty instead? In that case it would be:

({PurchasedLogs.CurDeliveryPad} = "" or
{PurchasedLogs.CurDeliveryPad} = {?Select Pad})

You need to find out exactly what is in the field. It could be one blank space, or more.

Lisa

 

[2ffat]

Yes, they are NULLs.

James P. Cottingham

^{There's no place like 127.0.0.1.
There's no place like 127.0.0.1.}

 

[lyanch]

You have to use isnull for the excluded as well..



(isnull({PurchasedLogs.CurDeliveryPad}) or
({PurchasedLogs.CurDeliveryPad} = {?Select Pad} and
{PurchasedLogs.CurDeliveryPad} <> {?Exclude Pad}))


I would try to run the SQL in a query tool, and see if the data is correct there.. or if it is a crystal thing.

Lisa

 

[2ffat]

It's definitely a CR thing. I'll try your last suggestion later. Thanks.


James P. Cottingham

^{There's no place like 127.0.0.1.
There's no place like 127.0.0.1.}

 

[2ffat]

Tried it. Still no change in the report. It has something to do with the exclusion. If I didn't have to exclude any pads, the report would work correctly.


James P. Cottingham

^{There's no place like 127.0.0.1.
There's no place like 127.0.0.1.}

 

[lyanch]

Try converting the nulls to empty strings in a SQL expression:

Something like (depending on your database)

//%NulltoString
isnull(PurchasedLogs.CurDeliveryPad,"")

Then change your selection formula to:

{%NulltoString} in ({?Select Pad},"") and
{%NulltoString} <> {?Exclude Pad} and

It is sometimes a pain to play with these until crystal "gets them" but there usually is a way.

Lisa

 

[synapsevampire]

Crystal will include or exclude nulls correctly, this isn't a CR thing.

(
isnull({PurchasedLogs.CurDeliveryPad})
or
{PurchasedLogs.CurDeliveryPad} = 0
)
or
(
{PurchasedLogs.CurDeliveryPad} = {?Select Pad} and
{PurchasedLogs.CurDeliveryPad} <> {?Exclude Pad})
)

Note that the isnull check must be placed in the beginning of the formula.

Rather than posting that something didn't work, please post what you tried as Lisa seems to be on track given what you've supplied.

-k

 

[2ffat]

Thanks for your responses. This is so frustrating for several reasons. First, there should never be a NULL pad which is why I wrote the reports the way I did originally (see original posting). I would like to see those responsible for NULL pads try to explain to the auditors why logs are delivered but never put anywhere: "Oh, we just left them in the air so they really aren't on any pad." ARGGG.

Second, I don't have alot of time to figure this out.

Third, logically, the above should work and I don't know why it won't. (Which is why I posted this in the first place.)

Here's what I can do. If I want only those logs on a NULL pad, the following two codes work:

(Len({PurchasedLogs.CurDeliveryPad}) = 0

or

isnull({PurchasedLogs.CurDeliveryPad}

If I want logs on the NULL pad and a selection of pads, the following codes work:

(Len({PurchasedLogs.CurDeliveryPad}) = 0 or {PurchasedLogs.CurDeliveryPad} = {?Select Pad})

or

(isnull({PurchasedLogs.CurDeliveryPad}) or
{PurchasedLogs.CurDeliveryPad} = {?Select Pad})

If I want the logs on the NULL pad and I want to exclude certain pads, the following also works:

(Len({PurchasedLogs.CurDeliveryPad}) = 0 or
{PurchasedLogs.CurDeliveryPad} <> {?Exclude Pad})

or

(IsNull({PurchasedLogs.CurDeliveryPad})or
{PurchasedLogs.CurDeliveryPad} <> {?Exclude Pad})

But when you try to include and exclude and get NULL pads, then the NULL pads don't show up. (See original posting.)

Unless someone can wave a magical wand and give me some brains, I'll split the reports I've written into two kinds. Those that only exclude certain pads and those that include certain pads. Thats how they run then anyway. It just means I have to maintain more reports. Oh well.

Thanks again.

James P. Cottingham

^{There's no place like 127.0.0.1.
There's no place like 127.0.0.1.}