Nested IIF expr not evaluating 2nd condition and beyond.

[Jim Dudley]

I have a report with 2 group levels. The fist group organizes data on
Workshops attended by Student #(unique 9 digit value)The second group level
organizes data on 5 different Types of Workshops and counts the # of records
for each Type.
The Page Footer then Totals the Counts of each group.

The above two items are group & sorting and returning the values I require
accurately.

I need a 3rd calculation from the Totals of the second group. Students earn
credits for each Workshop and earn College Certificates at 5 different Levels
base on accumulated credits.

I have inserted in the Group Fitter an expression in an Unbound Label
The first expression works and returns Level 1 for any student with 9 or
more credits, the subsequent conditions are not evaluated except for the
,Otherwise Expression at the end. If a student has less than 9 credits "N/A"
is returned.
I want the IIF stmt to return Level 2, 3, 4, or 5 for those students who
have accumulated the required credits.

This is my stmt: =IIf(Sum([Workshop_Registrations]![Credits])>=9,"Level 1",
IIf(Sum([Workshop_Registrations]![Credits])>=17,"Level 2",
IIf(Sum([Workshop_Registrations]![Credits])>=25,"Level 3",
IIf(Sum([Workshop_Registrations]![Credits])>=33,"Level 4",
IIf(Sum([Workshop_Registrations]![Credits])>=40,"Level 5","N/A")))))

Any comments on correcting this statement would be greatly appreciated.

Thanks in advance...

Jim

 

[John Spencer]

Either reverse the tests or use a range in each test. Think about it
17 is greater than 9 so 17 is going to fall into Level 1.

This will probably do what you want.

=IIf(Sum([Workshop_Registrations]![Credits])>=40,"Level 5",
IIf(Sum([Workshop_Registrations]![Credits])>=33,"Level 4",
IIf(Sum([Workshop_Registrations]![Credits])>=25,"Level 3",
IIf(Sum([Workshop_Registrations]![Credits])>=17,"Level 2",
IIf(Sum([Workshop_Registrations]![Credits])>=9,"Level 1","N/A")))))

Or using a range.
=IIf(Sum([Workshop_Registrations]![Credits])>=9 and Credits<17,"Level 1",
IIf(Sum([Workshop_Registrations]![Credits])>=17 and Credits<25,"Level 2",
IIf(Sum([Workshop_Registrations]![Credits])>=25 and Credits<33,"Level 3",
IIf(Sum([Workshop_Registrations]![Credits])>=33 and Credits<40,"Level 4",
IIf(Sum([Workshop_Registrations]![Credits])>=40,"Level 5","N/A")))))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jim Dudley]

Thank you John, it works perfectly now. And your solution once read makes
perfect sense. I guess I have been retired to long and become more than a
little Rusty at this stuff.

Have a Great Day!!!
--
Thanks in advance...

Jim

Either reverse the tests or use a range in each test. Think about it
17 is greater than 9 so 17 is going to fall into Level 1.

This will probably do what you want.

=IIf(Sum([Workshop_Registrations]![Credits])>=40,"Level 5",
IIf(Sum([Workshop_Registrations]![Credits])>=33,"Level 4",
IIf(Sum([Workshop_Registrations]![Credits])>=25,"Level 3",
IIf(Sum([Workshop_Registrations]![Credits])>=17,"Level 2",
IIf(Sum([Workshop_Registrations]![Credits])>=9,"Level 1","N/A")))))

Or using a range.
=IIf(Sum([Workshop_Registrations]![Credits])>=9 and Credits<17,"Level 1",
IIf(Sum([Workshop_Registrations]![Credits])>=17 and Credits<25,"Level 2",
IIf(Sum([Workshop_Registrations]![Credits])>=25 and Credits<33,"Level 3",
IIf(Sum([Workshop_Registrations]![Credits])>=33 and Credits<40,"Level 4",
IIf(Sum([Workshop_Registrations]![Credits])>=40,"Level 5","N/A")))))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a report with 2 group levels. The fist group organizes data on
Workshops attended by Student #(unique 9 digit value)The second group level
organizes data on 5 different Types of Workshops and counts the # of records
for each Type.
The Page Footer then Totals the Counts of each group.

The above two items are group & sorting and returning the values I require
accurately.

I need a 3rd calculation from the Totals of the second group. Students earn
credits for each Workshop and earn College Certificates at 5 different Levels
base on accumulated credits.

I have inserted in the Group Fitter an expression in an Unbound Label
The first expression works and returns Level 1 for any student with 9 or
more credits, the subsequent conditions are not evaluated except for the
,Otherwise Expression at the end. If a student has less than 9 credits "N/A"
is returned.
I want the IIF stmt to return Level 2, 3, 4, or 5 for those students who
have accumulated the required credits.

This is my stmt: =IIf(Sum([Workshop_Registrations]![Credits])>=9,"Level 1",
IIf(Sum([Workshop_Registrations]![Credits])>=17,"Level 2",
IIf(Sum([Workshop_Registrations]![Credits])>=25,"Level 3",
IIf(Sum([Workshop_Registrations]![Credits])>=33,"Level 4",
IIf(Sum([Workshop_Registrations]![Credits])>=40,"Level 5","N/A")))))

Any comments on correcting this statement would be greatly appreciated.

Thanks in advance...

Jim