Nexted if and use of max function - help with syntax, please

[TAM]

Can you help me debug this statement? I'm trying to use the "max" function so that I can decrease the number of if statements. Here's my defined field:



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))))

The error I receive: Expression you entered has a function containing the wrong number of arguments.

[ranman256]

not Max, use DMAX
Dmax has 3 params: DMax(field, table, where)

and your where clause makes no sense:
Dmax( [br_qual_rate], [rate], [abc_leader_rate]????

[John_G]

Your problem is this : max([br_qual_rate],[rate],[abc_leader_Rate],0)

Access does not have a function that returns the maximum of an arbitrary number of arguments. Access SQL has a Max function, but it does not work like that, either.

I think you will need to use Iif functions here.