Formula for age range but can't count blanks 1

[Pete271]

Hi

I'm on cr 8.5. I have the following formula

if {CnBio.CnBio_Age} in 18 to 25
then "18-25" else
if {CnBio.CnBio_Age} in 26 to 30
then "26-30" else
if {CnBio.CnBio_Age} in 31 to 45
then "31-45" else
if {CnBio.CnBio_Age} in 46 to 60
then "46-60" else
if {CnBio.CnBio_Age} in 61 to 80
then "61-80" else
if {CnBio.CnBio_Age} >= 81
then "81+" else
if {CnBio.CnBio_Age} = 0.00 then
"Unknown"

but the last statement is not picking up the blanks. I have also tried

if {CnBio.CnBio_Age} = " " then "Unknown"

but that didn't work either.

All advice gladly received.

Many thanks
Pete

 

[gj0519]

It's possible that you have null values.
Try this for your last line.

If(IsNull({CnBio.CnBio_Age}) Or {CnBio.CnBio_Age} = '')
   Then 'Unknown'

GJ

 

[Bloke152]

I think it might be because the blanks are nulls?
try checking for nulls also, eg

if isnull({CnBio.CnBio_Age}) then ..

 

[Pete271]

Thanks for the suggestions.

My cross-tab report now looks like this

col 1 col 2 col 3

123 12 12
0 0 1
18 - 25 123 123 123
26 - 30 123 123 123
...
...
Unknown 0 0 0

so now I'm really confused!!

the first two rows have no headings.

 

[lbass]

Try changing the formula to:

If IsNull({CnBio.CnBio_Age}) Or
trim({CnBio.CnBio_Age}) = "" Then
'Unknown' else
if {CnBio.CnBio_Age} < 18 then
"0-17" else
if {CnBio.CnBio_Age} < 26 then
"18-25" else
if {CnBio.CnBio_Age} < 31 then
"26-30" else
if {CnBio.CnBio_Age} < 46 then
"31-45" else
if {CnBio.CnBio_Age} < 61 then
"46-60" else
if {CnBio.CnBio_Age} < 81
then "61-80" else
if {CnBio.CnBio_Age} >= 81
then "81+"

This allows you to capture people whose ages contain decimals. Or you could use the round() function on the age field.

-LB

 

[Pete271]

excellent!

I had to swap the 'trim()' with 'round()' and then it worked perfectly.

And thanks for including the bit for Under 18's - that was the second blank row.

Pete