Help with IIF statement

[matthewR]

Access 2003

I have the following IIF statement but it is saying I have the wrong amount of arguments:

Indicator:IIF([Vis Rating Method]<> [Medical Rating Method] or IIf([Den Rating Method] <> [Medical Rating Method]or IIF([Den Rating Method]<>[Vis Rating Method],"Mixed","Not Mixed")))

[HansV]

You only need one IIf:

Indicator:IIf([Vis Rating Method]<>[Medical Rating Method] OR [Den Rating Method]<>[Medical Rating Method] OR [Den Rating Method]<>[Vis Rating Method],"Mixed","Not Mixed")

Alternatively:

Indicator:IIf([Vis Rating Method]=[Medical Rating Method] AND [Den Rating Method]=[Medical Rating Method],"Not Mixed","Mixed")

[matthewR]

Thanks Hans.
I have another question. I have three fields - Medical Rating Method, Den Rating Method and Vis Rating Method. When I group and bring everything up to the Client level, there could be more than one rating Method under the Medical Rating Method. If they are different, I want them to say "Mixed" also. Right now it is saying Not mixed when there is more than one rating under Medical.

Client Number	Client Name	Medical Rating Method	Vis Rating Method	Den Rating Method
000141	Ampco Pittsburgh Corporation	Steel Pensioner Major Medical
000141	Ampco Pittsburgh Corporation	ASO

Maybe this isn't possible the way I have it. Maybe I should make a Medical Rating Method2 field?

[HansV]

The best design would be to have only one rating method field plus a rating type field:

Client Number	Client Name	Rating Method	Rating Type
000141	Ampco Pittsburgh Corporation	Steel Pensioner Major Medical	Medical
000141	Ampco Pittsburgh Corporation	ASO	Medical
000142	Acme Inc.	Who knows	Vis
000143	ZZZ Company	Dunno	Den

You could then create a totals query that groups by Client Number and counts the Rating Method field. If the count is > 1, it's mixed, otherwise not mixed.

[matthewR]

Thanks Hans. I reconfigured to your design and it works.