Conditional Arguments in a Report Text Box 2

[rj51cxa]

In a particular shooting competition teams shoot for prizes (NH) or as “Honours Only” (HO). In my published prize list, I need to show the winners and second place, for the award of trophies, but also the HO teams if they either win the competition of come second.

An example of the final results sheet should look like this:

Nominal Winners Team A (HO) Total (e.g. 100)
Winners Team B (NH) Total (e.g. 99)
Nominal Second Place Team C (HO) Total (e.g. 98)
Second Place Team D (NH) Total (e.g. 97)

The report is based on a Union Query which selects the top 2, based on whether control “Honours Only” is true or false. This query produces between 2 and 4 results, depending on how many teams are shooting as HO.

The fields used in the report are:
"Team", "Total" (total score) and "Type" (“HO” or “NH”)

The report is sorted on "Total" which gives a "Position" between 1 and 4, with the highest scoring team being given position 4. If there are only three teams (i.e. only one shooting as “HO”, then the highest team is given position 3. If no teams are shooting as “HO”, the highest team is given position 2, with the second place team being given position 1.

In order to show which team is the winner of what (as above), I am trying to use a text box with a series of IIf statements which result in the text “Winners” etc. being displayed, but the permutations and combinations become impossible to work out.

There has to be an easier way but I can’t see it. If anyone can see a way of doing it, I would be most grateful.

Best Regards
John

 

[genomon]

I am trying to use a text box with a series of IIf statements

Scrap the IIf's.
Try putting some VBA in the Detail Print or Format sections where you set the test box (or label caption() = whatever you want with If Then.... or Select Case... statements.

Ever notice how fast Windows runs? Me neither.

 

[rj51cxa]

Thanks Genomon,

I'll have a go at that and come back to you if I get stuck.

 

[rj51cxa]

Genomon,

Afraid I'm stuck because of my somewhat limited knowledge of VBA.

I went into the Detail Print Section of the report and tried to write a simple Select Case statement to see how it worked. The problem was that I could not get the result to display. I created a label (Placing) and tried to specify in the code that the text should be placed in this label. No joy. I tried replacing the label with a text box but again, no joy. My code looked like this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim Position

Select Case Position
Case 4
[Placing] = "Winner"
Case 3
[Placing] = "Runner Up"
Case Else
[Placing] = ""
End Select

End Sub

What I was trying to display was that if position = 4 then the label would display "Winner". If position = 3 then the label would display "Runner Up", otherwise it would display nothing.

If you could help me get this simple code to work, then I should be able to build on it with the other conditions.

Thanks a lot
John

 

[genomon]

Position has no value here. What is it supposed to be?
"[Placing]" is what? Brackets usually denote a field name in the recordsource. Try naming the controls txtPlacing, or lblPlacing depending on whether they are labels or text boxes. If you go with labels, then you need to set the caption property: lblPlacing.Caption = "whatever".

Ever notice how fast Windows runs? Me neither.

 

[rj51cxa]

Genomon

[Position] is a text box in the report which is based on the sorting and grouping and used to organise the sequence in which the teams are reported. Hence the winning team is position 4 and the second place is position 3.

I'll change the name of [placing] but would appreciate some help with the systax of the Case statement. Just a few lines would help, based on my previous post. Once I can get the text printing out, I'll be OK.

Thanks a lot
John

 

[genomon]

If "Position" is a text box, you don't need the Dim statement (that is used for variable or object declarations). Otherwise, what you have ought to work. Let us know if it does (or doesn't) & we'll proceed from there.

Ever notice how fast Windows runs? Me neither.

 

[rj51cxa]

Sorry, Genomon, still no joy. My code now looks like this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Select Case Position
Case 4
lblPlacing = "Winner"
Case 3
lblPlacing = "Runner Up"
Case Else
lblPlacing = ""
End Select

End Sub

One thought - should I be seeing anything when I preview the report? This is what I would like, so that I can check if it is OK. Could it be that I am putting the code in the wrong place?

John

 

[genomon]

You should be able to preview.
Since this is a label, you need to set its caption property:
lblPlacing.Caption = "Runner Up"

Ever notice how fast Windows runs? Me neither.

 

[rj51cxa]

Oh dear Genomon, not doing too well here.

I replaced lblPlacing with lblPlacing.Caption throughout but now I get an error message - Run-Time error 424 - Object Required. When I go to debug, I find that the lblPlacing.Caption = "" is highlighted. I tried putting text in between the double quotes but it makes no difference.

I tried replacing the label with a text box named txtPlacing and changed the code accordingly. This time the code ran but nothing appeared in the previewed report.

Sorry to be a pain but I need a bit more help.
John

 

[genomon]

Sounds like you may have forgotten to set the name property of the label, or perhaps mis-spelled it in the property sheet.
There is no caption property for a text box, which I think would prevent a successful compile if it were coded that way.
Try running the code in break mode and hover the cursor over "position" to see what's in it (or add a message box). This is really really simple stuff. Did you check to make sure the visible properties of the labels/text boxes are set to yes?

Ever notice how fast Windows runs? Me neither.

 

[rj51cxa]

I've checked everything you suggested and everything is correct. If I type some text into the caption of the label it will show up. I had a text box before, with an IIf statement which referred to "Position" and this worked OK.

I re-wrote the code as follows:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Select Case Position
Case 4
lblPlacing.Caption = "Winner"
Case 3
lblPlacing.Caption = "Runner Up"
Case Else
lblPlacing.Caption = "Not placed"
End Select

End Sub

Now I'm back to it asking for an object. This is what I don't understand because if I rem out the case else, it stops at Case 3 with the same error message. Why is it asking for an object when the caption field requires
only text?

 

[genomon]

The caption property requires a label object.
For some reason this report doesn't believe there is a label object named "lblPlacing". This label is in the report detail section, and not some subreport, right?

Ever notice how fast Windows runs? Me neither.

 

[rj51cxa]

Yes, lblPlacing is part of the detail section. I've removed the label and added a new one. When I add it, I had to add the caption - Placing. I then went into the object properties, renamed it as Placing and removed the caption, but I still get the same error message asking for an object

 

[genomon]

I then went into the object properties, renamed it as Placing

And therein lies the problem. Rename it lblPlacing, not Placing.

Ever notice how fast Windows runs? Me neither.

 

[rj51cxa]

Thanks Genomon, you've cracked it for me. Have two stars!
I really do appreciate your patience and the time you have given me.

Now all I have to do is to sort out the rest of the conditions.

Best wishes
John

 

[genomon]

Thanks, and yee ha!
Good luck!

Ever notice how fast Windows runs? Me neither.