If / Then / Else Problem - CR Pro 9.2.0.448 1

[SgtRudeDog]

I have a problem with a standard formula using if then - basically it won't display the ELSE part of the formula:
if {SPE0304.T1} = "F3" or {SPE0304.T2} = "F3" or {SPE0304.T3} = "F3" or {SPE0304.T4} = "F3" then
1
else
2;
sample data:
F3,F41,, RESULTS IN 1
F41,F3,F34, RESULTS IN 1
F3,,, RESULTS IN 1
F41,,, RESULTS IN BLANK INSTEAD OF 2

Is this a bug or am i just being daft??? I've tried writing this in Crystal, Basic, and with and without a variable but all result in the else part being missed out. The formula is a number result, and the sample data is a text field.
Any help would be appreciated as we are migrating from Office to Crystal and I don't have this problem in VBA.
Cheers
Ian

 

[SgtRudeDog]

oh, i forgot if data is e.g F41,F42,F9,F5 i.e all fields are populated, then I DO get a result of 2, but I need it all the time.
cheers
Ian

 

[wcburton]

A NULL value will cause the formula not to work as expected. You can set "convert nulls to default value" in the report options so that null text becomes blank and null numbers become 0. If you don't want NULLS converted for every field in the report then you need to test for nulls in the formula.

Someone with more experience than I have can probably illuminate further on setting up the formula to handle NULL values if needed.

 

[SgtRudeDog]

That worked a treat - i'll figure out treating null values - just a very strange thing to do for a formula to do if not true to ignore - never mind - i'm learning every day.
Thanx wcburton.


--- Paintball is not a game : it's a way of life ---

 

[FVTrainer]

wc is right. In your case, because of all the "OR" logic, it might be easier to test for NULL by writing a 2nd formula that references the first formula and displaying it rather than the first formaula. This second formula would look like:

If IsNull({@FirstFormula) then 2 else {@FirstFormula}

 

[Ngolem]

You must test for nulls first before proceeding with the reset of the formula...otherwise crystal will crash at the first existance of a NULL.

This can be a hard bug to track down sometimes since Crystal does execute until it reaches the NULL

eg.

WhilePrintingRecords;
NumberVar x := x + 1;
StringVar y;

if {table.value} = "A" then
y := "A"
else
y := "B";

if you have nulls and later display x and y, will see x increase incrementally but you will see repeats of the last y value when {table.value} is NULL...Why??? because the formula executed until it hit the if-then block.

in debugging the formula YOU THINK THE FORMULA WORKED because of the increment of x but this is not true in reality.

So where you know Nulls can occur you must test them early...in your case the formula should be something like

if (not isnull({SPE0304.T1}) and {SPE0304.T1} = "F3" ) or
(not isnull({SPE0304.T2}) and {SPE0304.T2} = "F3" ) or
(not isnull({SPE0304.T3}) and {SPE0304.T3} = "F3" ) or
(not isnull({SPE0304.T4}) and {SPE0304.T4} = "F3" ) then
1
else
2;

That should give you the result you want

Jim Broadbent

 

[SgtRudeDog]

thanx 2 all have replied - v helpful - i can see this crystal programming not being quite as forgiving as VBA - oh well - another language to learn - wot fun.
Cheer again
Ian

--- Paintball is not a game : it's a way of life ---