Force Null Value 2

[dunkyn]

I want to force a null value if the name of a specific field is say, "APPLES", because I need to calculate an average on the outcome, and I want to exclude "APPLES".
Is this possible?

If {sp.name}="APPLES"
then NULLVALUE
{sp.REQFIELD}

 

[synapsevampire]

Crystal doesn't create null values in formulas, you would have to use something like:

If {sp.name}="APPLES" then
""
else
{sp.REQFIELD}

-k

 

[dunkyn]

Sorry - forgot to mention that the field is numeric and I need a null value, not a zero, so that I can calculate an average on the result.

 

[synapsevampire]

Use a Running Total with an evaluate use a formula.

-k

 

[dunkyn]

Got it. Thank you!

 

[kenhamady]

There is a little known technique for forcing an if-then formula to return a null value (instead of zero or empty string). The advantage over using a running total is that with a regular summary field you can print totals in the header and use Group Selection. If you ever need to use the technique it is desribed in:

http://www.kenhamady.com/form14.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

Ken: I remembered that you had a trick for this, I just couldn't find it.

 

[lyanch]

Hmm.. thanks ken i never thought of that. My particular method has been to "create" a null value field with a SQL expression.

ie in sybase
%NullInt

convert(int, NULL) for a null integer ..etc.. these add negligible DB processing time even for large datasets..

Then in my if then else I set it = to the null field

like

if X=Y then {table.int} else %NullInt

Just another method of getting there..

Lisa

 

[dunkyn]

many thanks!