Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Null Fields and Null Values (Crystal Syntax)

In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.

Relating to operators, when Crystal Reports evaluates the condition:


IsNull({Product.Color}) Or
InStr({Product.Color}, " ") = 0

It first evaluates IsNull ({Product.Color}), and when it determines that this is True, it knows that the whole condition is True, and thus does not need to check whether


InStr({Product.Color}, " ") = 0

In other words, Crystal Reports will stop evaluating a Boolean expression when it can deduce the results of the whole expression. In the following example, the formula guards against attempting to divide by zero in the case that denom is 0:


Local NumberVar num;
Local NumberVar denom;
...
If denom <> 0 And num / denom > 5 Then
...

The {Product.Color} field contains both basic colors such as "red" and "black" and fancy two word colors such as "steel satin" and "jewel green". Suppose that you want to write a formula that writes out "basic" for the basic colors and "fancy" for the others.


If InStr({Product.Color}, " ") = 0 Then
   "basic"
Else
   "fancy"

The function call to InStr searches the {Product.Color} string for a space. If it finds a space, it returns the position of the space, otherwise it returns 0. Since basic colors are only one word with no spaces, InStr will return 0 for them.

For some products, such as the Guardian Chain Lock, a color value was not recorded and so the {Product.Color} field has a null value in the database for that record. Thus, the Guardian Chain Lock record does not have any word printed beside it.

Here is how to fix up the previous example using IsNull:


If IsNull({Product.Color}) Or 
   InStr({Product.Color}, " ") = 0 Then
   "basic"
Else
   "fancy"

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.