Complex IIf Expression in a Report

[rj51cxa]

My report has a text Box which has a Control source based on a series of IIf Statements as follows:

= IIf ([type]="chambre" and [position]=1,"chambre", IIf ([type]="chambre" and [position]=2,"vulture ",
 IIf ([type]="mersey" and [position]=1,"mersey",
 IIf ([type]="tyne" and [position]=1,"tyne", IIf ([type]="tyne" and [position]=2,"clover ",
 IIf ([type]="turtle" and [position]=1,"turtle", IIf ([type]="turtle" and [position]=2,"cornwall",
 IIf ([type]="centenary" and [position]=1,"president",
 IIf ([type]="nailman" and [position]=1,"nailman",
 IIf ([type]="golden trigger" and [position]=1,"golden trigger",
 IIf ([type]="cock" and [position]=1,"cock", IIf ([type]="cock" and [position]=2,"tappet hen",
 IIf([Type]="fibua" And [position]=1,"Thames",
 IIf([Type]="M8" And [Position]=1,"V. Elvedon ",
 IIf([Type]="Roberts" And [position]=1,"D of Westminster",
 IIf([Type]="Roupell" And [Position]=1,"Graham","")))))))))))))

The statement is fine if I reduce the number of IIf Statements (I think 12 is the maximum) but I need to have all of these arguments in complete the report.
I have a feeling that it might be possible to use a VBA statement using the "On Print" event from the Detail header, but I can't find a way of building it.
Any help would be much appreciated.
Thanks a lot
John

 

[dhookom]

Have you defined any lookup fields in your tables? I created a table just as yours and the first two lookups returned Chambre as expected and the third returned Vulture as expected. Try again.

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Thank you.

 

[GingerR]

Hey there--aren't we missing some double-quotes above?

=DLookUp("[txtTrophy]","[TblSignature]","[TxtComp] = '" & [TxtComp] & "' and [TxtPosition] = " & [TxtPosition])

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

For the sake of closure, yes there were some missing quotes.

Duane
Hook'D on Access
MS Access MVP