Dealing with Nulls

[DerPflug]

I have the following formula in my crystal report done in .net:

<code>
(If {?Electric Rate} = 'All' then {MyTable.RateCode}={MyTable.RateCode}
else UpperCase({?Electric Rate}) = UpperCase({MyTable.RateCode}))
</code>

When I run this report choosing 'All' it won't pull in records where MyTable.Ratecode is null. How can I alter this formula to bring in records where MyTable.RateCode is null. Thanks in advance.

 

[lbass]

Try rewriting it like this:

{?Electric Rate} = 'All' or
UpperCase({?Electric Rate}) = UpperCase({MyTable.RateCode})

-LB

 

[DerPflug]

Leaving out the &quot;if,then,else&quot; portions?

 

[synapsevampire]

First, create a SQL Expression which converts the field to uppercase, then create a formula which uppercases your parameter, then use the comparison of the SQL Expression to the formula, this will ensure SQL Pass through to the database, a standard uppercase in a record selection will not get passed to the database, punishing your performance.

In Oracle it would be:

Upper({MyTable.RateCode})

Next, create a record selection formula akin to:

If {?Electric Rate} <> 'All' then
{@upperedformula} = {%upperedfield}
else {?Electric Rate} = 'All' then
true

I know, the *else if* seems superfluous, but it's benficial to providing proper SQL pass through.

This will bring all rows in the event they select All, or it will match the uppercased formula vs. the SQL Expression.

-k