Using the max function in a nested if statement - error "wrong number of arguments"

[TAM]

Can you help me debug this statement? I get the error "Expression you entered has a funciton containing the wrong number of arguments.

BetterofRate: IIf([position]=3,0,IIf([position]=1 And [Grid_Assignment]="Locked",[base_rate],IIf([position]=50 And [Grid_Assignment]="Locked",[base_rate],iif(([position]=1,max([br_qual_rate],[rate],[abc_leader_rate]),iif(([position]=50,max([br_qual_rate],[rate],[abc_leader_Rate],0))))

 

[xenou]

there is no max function in msaccess (unless it is in a sql select clause). So that's probably a problem.

 

[xenou]

Possibly:

BetterofRate: switch(
position=3,0,
(position=1 OR position=50) AND Grid_Assignment="Locked",base_rate,
(position=1 OR position=50) AND br_qual_rate > rate and br_qual_rate > abc_leader_rate, br_qual_rate,
(position=1 OR position=50) AND rate > abc_leader_rate, rate,
1=1, abc_leader_rate
)

If position is not 1, 3, or 50 you will always get abc_leader_rate (that's the default here for anything that doesn't qualify otherwise). Also I would assume that one of either rate, br_qual_rate, and abc_leader_rate is a max so no need for the zero I think.