isnull formula

[Jonathan212]

Need help with a formula that I am thinking some sort of isnull statement would be applicable.

I am using the following formula to label turnover time in a field:

if {@TOT} >=0 and {@TOT} <10 then "0 to 9 minutes" else
if {@TOT} >9 and {@TOT} <20 then "10 to 19 minutes" else
if {@TOT} >19 and {@TOT} <30 then "20 to 29 minutes" else
if {@TOT} >29 and {@TOT} <40 then "30 to 39 minutes" else
if {@TOT} >39 and {@TOT} <50 then "40 to 49 minutes" else
if {@TOT} >49 and {@TOT} <60 then "50 to 59 minutes" else
if {@TOT} >59 then "in excess of 1 hour" else
"data time missing"

I have a few values each month that were not input to a field that I am using to derive TOT. The result is no value (the field is empty on the output).

I would like to show "data time missing" when there is no value in that field, but I can't figure how/where to include that expression in the formula shown above.

I am using CR10.

Thanks in advance, Jonathan

 

[synapsevampire]

Try:

if (isnull(@tot)
or
@tot = 0
"data time missing"
else
if {@TOT} >=0 and {@TOT} <10 then "0 to 9 minutes" else
if {@TOT} <20 then "10 to 19 minutes" else
if {@TOT} <30 then "20 to 29 minutes" else
if {@TOT} <40 then "30 to 39 minutes" else
if {@TOT} <50 then "40 to 49 minutes" else
if {@TOT} <60 then "50 to 59 minutes" else
if {@TOT} >59 then "in excess of 1 hour" else
"data time missing"

Isnulls must be performed first in formulas.

-k

 

[Bloke152]

you could add another and into your if statement
eg.
and not isnull{@TOT}

or you could put another if statement in to say

if isnull{@TOT} then 'Data Time missing'

Are you using the field that can be null to create {@TOT}? as it might be safer to put the isnull into the {@TOT} formula instead

 

[synapsevampire]

Sorry,:

if (
isnull(@tot)
or
@tot = 0
) then
"data time missing"
else
if {@TOT} >=0 and {@TOT} <10 then "0 to 9 minutes" else
if {@TOT} <20 then "10 to 19 minutes" else
if {@TOT} <30 then "20 to 29 minutes" else
if {@TOT} <40 then "30 to 39 minutes" else
if {@TOT} <50 then "40 to 49 minutes" else
if {@TOT} <60 then "50 to 59 minutes" else
if {@TOT} >59 then "in excess of 1 hour" else
"data time missing"

-k

 

[synapsevampire]

I think the nulls are generally converted to zero, so once again:

if (
isnull(@tot)
or
@tot = 0
) then
"data time missing"
else
if {@TOT} >=1 and {@TOT} <10 then "0 to 9 minutes" else
if {@TOT} <20 then "10 to 19 minutes" else
if {@TOT} <30 then "20 to 29 minutes" else
if {@TOT} <40 then "30 to 39 minutes" else
if {@TOT} <50 then "40 to 49 minutes" else
if {@TOT} <60 then "50 to 59 minutes" else
if {@TOT} >59 then "in excess of 1 hour" else
"data time missing"

-k

 

[Jonathan212]

Thanks Synapsevampipre and Bloke.
I would like to use the first suggestion, but I am getting an error message:

The ) is missing

Where can I place the ) that might be missing from the expression?

Thanks very much for the wonderful timeliness.

 

[synapsevampire]

Check the latter posts...

-k

 

[Jonathan212]

Soory, our messages were crossing and your solution works perfectly. Thanks so much Synapsevampire and Bloke. Very much appreciated. Jonathan