Multi-step IIF statement in Access 3

[kittean]

help .. it says that i have too many arguements. I know this type of structure works in Excel but i am having a problem bringing it over to Access.

Expr1: IIf([Age at start of this class]<16,"no service", IIf( [Age at start of this class]=16 or[Age at start of this class]<19),"16-18",IIf([Age at start of this class]=19 or [Age at start of this class]<25),"19-24",IIf(or([Age at start of this class]=25 or [Age at start of this class]<45,"25-44",IIf(Or([Age at start of this class]=45 or [Age at start of this class]<60,"45-59",IIf(or([Age at start of this class]=60 or [Age at start of this class]<150,"over 60","false")))))))))

 

[PWise]

i would create a table with three fields
1)agestart
2)ageend
3)catagory

and join on age between age start and ageend

 

[dhookom]

I agree with PWise (as usual). Even in Excel, I would create a table and use VLookup() rather than an unruly expression of nested if statements.

Data belongs in tables, not expressions or code. I would hate to maintain this if [red]when[/red] the age ranges change.

At the very least, I would create a small user-defined function that accepts the [age at the start of this class] and returns the range name. Save the function in a module named "modBusinessCalcs". At least this allows you to use the function anywhere, maintain it in one place, enter comments, format the code with comments and error handling, etc.

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Duane:
Thanks for the complement

 

[dhookom]

You're welcome PWise but I believe I just disagreed with you in thread701-1661460 ;-)

Duane
Hook'D on Access
MS Access MVP