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

 

[GingerR]

How about putting all of that data into a table and using that instead? You can join the table to the recordsource of your report and simply output the result. Then in the future you can simply update the table instead of hardcoding it into the report design.

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]

I agree with GingerR. There is some additional support for this solution at Data belongs in your tables -- not in your code

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Thanks GingerR and Duane, I see what you are getting at but I'm a bit out of my depth.

My report is based on a Union Query which combines the results of three other Union Queries, as follows:

SELECT Type, TxtUnit, Total, BisleyYear
FROM QryResultIndividual;
UNION SELECT Type, TxtUnit, Total, BisleyYear
FROM QryMisc;
UNION SELECT Type, TxtUnit, Total, BisleyYear
FROM QrySignature_Team;

The results of this query appear as follows (I have ommitted BisleyYear for clarity)

Type TxtUnit Total
CENTENARY HMS PRESIDENT (A) 1
CHAMBRE HMS KING ALFRED 659
CHAMBRE HMS PRESIDENT (A) 739
Cock HMS PRESIDENT (A) 2462
Cock HMS VIVID 2854
FIBUA HMS VIVID 1105
GOLDEN TRIGGER HMS KING ALFRED 0
M8 HMS VIVID 569
MERSEY HMS KING ALFRED 604
NAILMAN AIR BRANCH 0
PETERS HMS PRESIDENT (A) 739
PETERS HMS PRESIDENT (B) 637
Roberts HMS PRESIDENT (A) 656
Roupell HMS VIVID 540
TURTLE HMS CERES 554
TURTLE HMS PRESIDENT (A) 622
TYNE HMS KING ALFRED 760
TYNE HMS VIVID 963

The report is grouped by Type and sorted by Total. The Detail section contains TxtPosition (based on the running sum over the group and Hidden), TxtUnit and a third text box which displays the trophy name, based on the results of the IIf expressions above.

As you can see, certain Types have two results (the winners and runners-up each receive a trophy based on Position being either 1 or 2), hence the double IIf statements in my original code. The Types that have only one result have been given a Total of 1 or 0 so that the fields in the Union Queries match. They are the ones with the single IIf expression.

If the data is to be stored in a table and used to create the report, the table needs to display the TrophyName against TxtUnit, based on the highest or lowest Total. This was where the IIf expressions came from.

My problem is twofold - firstly I seem to be unable to make a table from a Union Query and, secondly, even if I base my Report on the Table instead of the Query, I will still end up with the same number of IIf expressions to sort out which Unit wins which trophy in a particular group.

I hope this makes sense and that you can see a way around the problem.

Best Regards
John

My

 

[GingerR]

We're not saying to base the report on a query/table. base your TEXT BOX on the table.

If you put your data into a table, like this:

[b]ItemType    ItemPosition   ItemName[/b]
chambre          1          chambre
chambre          2          vulture

then join this table into your report's query to product your data for the text box, OR (i dont suggest this) use dlookup in the text box:

=dlookup("ItemName","ItemNames","ItemType = '" & [Type] & "' and ItemPosition = " & [Position])

...assuming Position is a number. Also I don't know if having TYPE as a control name will cause problems as i think that may be a reserved word in access. Anyhow, see if any of this works for you.

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

 

[rj51cxa]

Thanks GingerR,

I'll have a go but don't be surprised if I come back to you. I'm very much into uncharted territory here!

Best Regards
John

 

[rj51cxa]

Hi GingerR

I'm making progress. I created the table as you suggested

TblSignature
Type	TxtPosition	TxtTrophy
Centenary	1	President
Chambre	1	Chambre
Chambre	2	Vulture
cock	1	Cock
cock	2	Tappet Hen

I then tried to join this to my Union Query but I couldn't
find a way of doing it. If you can find time to show me how to do it, I would be grateful.

I had more success using the dlookup method. If I used the code as follows, the report gave me the trophy name alongside the Type but would not take account of position (i.e. 1 or 2)

=DLookUp("[txtTrophy]","[TblSignature]","[Type] =  Report![Type]")

I then tried to add the Position element into the code, as you demonstrated, but came unstuck. The code now looks like this:

 =DLookUp("[txtTrophy]","[TblSignature]","[Type] = "'& Report![Type] &"' and [TxtPosition] = " & Report![TxtPosition])

Each time I try to run the code, I receive a dialog box which says:

"The expression you entered contains invalid syntax. You may have entered an operand without an operator"

I would be grateful if you could take a look and see where I have gone wrong.

I'm not sure why you were not too keen on using dlookup. It seems very simple, so I would be grateful if you could explain your reasoning.

Best Regards
John

 

[dhookom]

What is the datatype of TxtPosition? Are they both the same in your tables? If it is text, you will need to use the single-quotes like you did with Type.

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Hi Dhookom,

Thanks for that. I've changed the name Type to "TxtComp" throughout, just to be correct, but my code still does not work. I think it is all to do with the single and double quotes, but I can't sort it out. I would be most grateful if you check my latest code and sort it out.

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

Thanks a lot
John

 

[dhookom]

So the data type of TxtPosition is what?

Although I don't like using DLookup() in a query, you might want to try:

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

I would think you could create a new query based on your final union query and tblSignature. Join the TxtComp and TxtPosition fields.

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Sorry, that ended up with a blank text box. TxtPosition in the report is a text box based on a running sum over the group, which I assumed to be numerical. I therefore made the data type of TxtPosition in my table numeric as well. When that didn't work, I then changed it to text because I thought the text box in the report was not numerical. Either way, all I get is either Error or nothing.

I'll try creating a new query as you suggested as see how that works

Thanks for your help
John

 

[rj51cxa]

Problem - I can only join the TxtComp fields as there is no TxtPosition in the final Union Query. TxtPosition is generated in the report based on TxtTotal for each group. The result is four results for each TxtComp instead of two. I tried different type of join but it makes no difference.

I think we might be back to dlookup - as long as we can get the syntax right!

 

[dhookom]

I missed the whole text box with running sum from any earlier post. If it is a running sum then it must be numeric.

Try get rid of the "Report!":

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

This assumes you have fields or text boxes in the report named txtComp (text datatype) and TxtPosition (numeric datatype).

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Sorry Duane, still comes out blank. I tried with and without "Report!" but it still doesn't work. It worked if I used a single comparison i.e. TxtComp with TxtComp but as soon as I try to add in the additional comparison it stops working.

Just to clarify I have text boxes in both the report and the table named txtComp (text datatype) and txtPosition (numeric datatype).

Hope this helps
John

 

[dhookom]

Do you want to look up the running sum value of txtPosition or the single value from the report's record source?

Is the Dlookup() text box in the same section of the report as the txtPosition text box?

What do you get if you hard-code the txtposition value?

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

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Duane,

TxtPosition generates either 1 or 2 (the underlying query has the top 2 from each competition) and the trophy allocated is based on the position. I therefore need to compare the running sum value (1 or 2) from TxtPosition with the data in TblSignature.

The DLookup text box, TxtPosition and TxtCompetition are all in the detail section of the report.

I tried your hard wired version but that resulted in a blank text box. The following code does work but shows the same trophy regardless of position:

=DLookUp("[txtTrophy]","[TblSignature]","[TxtComp] = [TxtComp]")

Sorry this is taking so long, I had no idea it was going to get so complicated.
John

 

[dhookom]

Do you have ever possible value of TxtComp in tblSignature as well as 1 and 2 in the TxtPosition field for each TxtComp? It looks like you have at least 12 unique values of TxtComp.

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Duane,

TblSignature looks like this:

TxtComp TxtPosition TxtTrophy
Centenary 1 President
Chambre 1 Chambre
Chambre 2 Vulture
cock 1 Cock
cock 2 Tappet Hen

As you can see, some competitions (TxtComp) have both 1st and 2nd place trophies, whereas others have only 1st place. Thus, for each Txt TxtComp there are either one or two TxtPositions and one or two TxtTrophies. This is an abbreviated version, there are actually 16 trophies to be awarded.

The report is grouped by TxtComp and sorted by the total score in each competition. This gives TxtPosition either 1 or 2.

The DLookup needs to look up both TxtComp and TxtPosition in order to return the correct trophy - hence my original double IIf statement

 = IIf ([txtComp]="chambre" and [position]=1,"chambre", IIf ([txtComp]="chambre" and [position]=2,"vulture "))

The expression produced the correct result but the problem was that, with the number of trophies to be awarded, the number of nested IIf statement exceed the limit.

Hope this clarifies things for you
Best Regards
John

 

[dhookom]

Open your debug window (press Ctrl+G) and enter these lines one at a time pressing the enter key after each line:

? DLookUp("[txtTrophy]","[TblSignature]","[TxtComp] = 'Chambre'")

? DLookUp("[txtTrophy]","[TblSignature]","[TxtComp] = 'Chambre' AND txtPosition=1")

? DLookUp("[txtTrophy]","[TblSignature]","[TxtComp] = 'Chambre' AND txtPosition=2")

What did you see?

If this didn't work, open a new, blank query and view the SQL View. Enter

SELECT [TxtTrophy]
FROM tblSignature
WHERE TxtComp = "Chambre" and txtPosition = 2

Switch to datasheet view and what do you see?

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

OK, The first expression produced nothing, the second produced "Chambre" and the third produced "Vulture". These two were the correct output.