Help with 'if then else' formula 1

[skystar70]

Hello.

I am on v 9 and have created the following formula:

If {st_wbs99.description} = "Extra Small" then
50000
Else If {st_wbs99.description} = "Small" then
100000
Else If {st_wbs99.description} = "Medium" then
250000
Else If {st_wbs99.description} = "Large" then
500000
Else if {st_wbs99.description} = "Extra Large" then
1000000
Else 0

I do not get an error when creating the formula. The problem is that I do not get a zero (last part of formula) on the records which do not conform to the other if statements. Meaning, my else statement does not seem to be working. The result is a null for those records without the Extra Small, Small, Medium, Large, or Extra Large designations.

Any ideas on why my last Else statement does not work??

Thanks!

Skystar

 

[lbass]

You must specifically test for nulls in the first clause of your if/then like this:

If isnull({st_wbs99.description}) then
0
Else If {st_wbs99.description} = "Extra Small" then
50000
Else If {st_wbs99.description} = "Small" then
100000
Else If {st_wbs99.description} = "Medium" then
250000
Else If {st_wbs99.description} = "Large" then
500000
Else if {st_wbs99.description} = "Extra Large" then
1000000
Else 0

-LB

 

[kskid]

Can it be null? If so, then you have to process it first.

If isnull({st_wbs99.description}) then
0
Else If {st_wbs99.description} = "Extra Small" then
50000
Else If {st_wbs99.description} = "Small" then
100000
Else If {st_wbs99.description} = "Medium" then
250000
Else If {st_wbs99.description} = "Large" then
500000
Else if {st_wbs99.description} = "Extra Large" then
1000000
Else 0

 

[skystar70]

Perfect. Yes, you were both right, it is a null and I always forget that nulls are special.

This worked great. Thank you!