is this query really too complex?

[Geoff Cox]

hello

I am getting the error message that this is too complex!

Can this be right? A limit on the number of IIFs?

Is there another way of doing this?

RenewalCost:
IIF([1118] and [type]="a",100,
IIF([1118] and [type]="c",60,
IIF([1118] and [type]="d",130,
IIF([1116] and [type]="a",85,
IIF([1116] and [type]="c",50,
IIF([1116] and [type]="d",100,
IIF([1618] and [type]="a",85,
IIF([1618] and [type]="c",50,
IIF([1618] and [type]="d",100,
IIF([1118plus] and [type]="a",100,
IIF([1118plus] and [type]="c",60,
IIF([1118plus] and [type]="d", 30,
IIF([1116plus] and [type]="a",85,
IIF([1116plus] and [type]="c",50,
IIF([1116plus] and [type]="d",100,0
)))))))))))))))


Thanks

Geoff

PS apologies for new post but thought that my question in previous
post might not be seen ....

 

[Douglas J. Steele]

It wouldn't surprise me if that's too much nesting of IIf statements.

The proper way to do it would be to normalize your tables. Having fields
name [1118], [1618] and [1116plus] is a sure sign that you haven't
normalized. For some good resources on this, see what Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

In the meantime, try

IIF([1118], Switch([type]="a",100,[type]="c",60,[type]="d",130),
IIF([1116], Switch([type]="a",85,[type]="c",50,[type]="d",100),
IIF([1618], Switch([type]="a",85,[type]="c",50,[type]="d",100),
IIF([1118plus], Switch([type]="a",100,[type]="c",60,[type]="d", 30),
IIF([1116plus], Switch([type]="a",85,[type]="c",50,[type]="d",100),0)))))

or

Switch([1118] = True, Switch([type]="a",100,[type]="c",60,[type]="d",130),
[1116] = True, Switch([type]="a",85,[type]="c",50,[type]="d",100),
[1618] = True, Switch([type]="a",85,[type]="c",50,[type]="d",100),
[1118plus] = True, Switch([type]="a",100,[type]="c",60,[type]="d", 30),
[1116plus] = True, Switch([type]="a",85,[type]="c",50,[type]="d",100),0))

 

[Geoff Cox]

It wouldn't surprise me if that's too much nesting of IIf statements.

The proper way to do it would be to normalize your tables. Having fields
name [1118], [1618] and [1116plus] is a sure sign that you haven't
normalized. For some good resources on this, see what Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

In the meantime, try

IIF([1118], Switch([type]="a",100,[type]="c",60,[type]="d",130),
IIF([1116], Switch([type]="a",85,[type]="c",50,[type]="d",100),
IIF([1618], Switch([type]="a",85,[type]="c",50,[type]="d",100),
IIF([1118plus], Switch([type]="a",100,[type]="c",60,[type]="d", 30),
IIF([1116plus], Switch([type]="a",85,[type]="c",50,[type]="d",100),0)))))

or

Switch([1118] = True, Switch([type]="a",100,[type]="c",60,[type]="d",130),
[1116] = True, Switch([type]="a",85,[type]="c",50,[type]="d",100),
[1618] = True, Switch([type]="a",85,[type]="c",50,[type]="d",100),
[1118plus] = True, Switch([type]="a",100,[type]="c",60,[type]="d", 30),
[1116plus] = True, Switch([type]="a",85,[type]="c",50,[type]="d",100),0))

Many thanks Douglas - I have tried the first of the 2 above and that
does the trick!

Food for thought re normalize etc!

Cheers

Geoff