Nested Select Case problem 1

[rj51cxa]

The report which utilises the code below is based on a query which returns up to four records, the top two teams competing for Honours Only (HO) and the top two who are not HO. If there is only one team competing as HO, the query will return only three records and if there are no teams competing as HO, then only two records are returned.

This code assumes there are four records all the time:

Public Function Placing(IntPosition As Integer, _
      BooHO As Boolean) As String

Select Case IntPosition
Case 4
    If BooHO Then Placing = "Actual Winner" Else Placing = "Winner"
Case 3
    If BooHO Then Placing = "Actual Second Place" Else Placing = "Second Place"
Case 2
    If BooHO Then Placing = "Actual Third Place" Else Placing = "Third Place"
End Select

Select Case IntPosition
Case 3
    If BooHO Then Placing = "Actual Winner" Else Placing = "Winner"
Case 2
    If BooHO Then Placing = "Actual Second Place" Else Placing = "Second Place"
Case 1
    If BooHO Then Placing = "Actual Third Place" Else Placing = "Third Place"
End Select
End Function

In order to make the code adjust to suit the number of records, I added a text box named “Count” to the report, which has a Control Source “=Count(*)”. This returns the number of records. As the number of teams reduces, the “Placing” will change, so I need to create a code that looks at IntCount as well as IntPlacing.

If we ignore the BooHO part of the argument for the moment, the argument is as follows:

If IntCount = 4 and IntPosition = 4 Then Placing = “Winner”.
If IntCount = 4 and IntPosition = 3 Then Placing = “Second Place”

If IntCount = 3 and IntPosition = 3 Then Placing = “Winner”.
If IntCount = 3 and IntPosition = 2 Then Placing = “Second Place”

If IntCount = 2 and IntPosition = 2 Then Placing = “Winner”.
If IntCount = 2 and IntPosition = 2 Then Placing = “Second Place”

My modified code currently looks like this:

Public Function Placing_2(IntPosition As Integer, _
      BooHO As Boolean, IntCount As Integer) As String

Select Case IntCount
Case 4
Select Case IntPosition
Case 4
    If BooHO Then Placing_2 = "Actual Winner" Else Placing_2 = "Winner"
Case 3
    If BooHO Then Placing_2 = "Actual Second Place" Else Placing_2 = "Second Place"
Case 2
    If BooHO Then Placing_2 = "Actual Third Place" Else Placing_2 = "Third Place"
End Select
End Select

Select Case IntCount
Case 3
Select Case IntPosition
Case 3
    If BooHO Then Placing_2 = "Actual Winner" Else Placing_2 = "Winner"
Case 2
    If BooHO Then Placing_2 = "Actual Second Place" Else Placing_2 = "Second Place"
Case 1
    If BooHO Then Placing_2 = "Actual Third Place" Else Placing_2 = "Third Place"
End Select
End Select
End Function

The query is currently returning 3 records and, if I run the code in the immediately window I get the correct answer. However, when I open the report, I see three records but the Text Box “Placing is blank”.
I would be grateful if someone could take a look and advise me why this should be.
Thanks a lot
John

 

[dhookom]

I would question the value of intCount in the report. Try change your code to the following and then look at the values in the debug window:

Public Function Placing_2(IntPosition As Integer, _
      BooHO As Boolean, IntCount As Integer) As String
Debug.Print "intCount: " & intCount
Select Case IntCount
  Case 4
    Select Case IntPosition
      Case 4
        If BooHO Then Placing_2 = "Actual Winner" Else Placing_2 = "Winner"
      Case 3
        If BooHO Then Placing_2 = "Actual Second Place" Else Placing_2 = "Second Place"
      Case 2
        If BooHO Then Placing_2 = "Actual Third Place" Else Placing_2 = "Third Place"
    End Select
  Case 3
    Select Case IntPosition
      Case 3
        If BooHO Then Placing_2 = "Actual Winner" Else Placing_2 = "Winner"
      Case 2
        If BooHO Then Placing_2 = "Actual Second Place" Else Placing_2 = "Second Place"
      Case 1
        If BooHO Then Placing_2 = "Actual Third Place" Else Placing_2 = "Third Place"
    End Select
  Case Else
    Placing_2 = "not 4 or 3"
End Select
End Function

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Duane,

I did as you suggested, with the following result:

?placing_2(3,True,3)
intCount: 3
Actual Winner

.
The report has three records and the highest scoring team was shooting for Honours Only. Looks like the coding is right but the text box in the report is showing nothing.
John

 

[rj51cxa]

Duane,

I missed you Case Else addition at the end of the code. When I put that into mine and ran the report, the text box showed "Not 4 or 3"

Something is getting through but not the right something.

John

 

[dhookom]

This is what I expected was happening. You shouldn't give the name of "Count" to a text box. It would be interesting to see the value of intCount from your actual report. I expect it is the count of the number of controls on your report.

Find a naming convention and use it. Avoid reserved words.

Immediately change the name from Count to txtCount. Where in your report is the text box? What is the control source of your text box that has the function as its control source?

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Duane,

I changed the name of the Count text box to RecordCount and amended IntCount to IntRecordCount but it made no difference. The code returend the correct answer in the immediate window.

Text Box RecordCount is in the Detail section of the report and has a control Source of =Count(*) which, according to the Help, displays the number of records. It actually does this and changes if I change the number of records.

The Detail section of my report has 7 Text Boxes as follows:
RecordCount
Position (based on the sorting and grouping)
Placing (this calls the Module)
Honours_Only (displays True or False)
TxtUnit (displays the Unit Name)
TxtHO (control source =IIf([Honours_Only]=True,"Honours Only",""))
Total (The total score)

As you can see, some have Txt as a prefix, other don't. I hear what you say about a naming convention and I will read up about this. What confuses me is that I seem to recall from previous posts that there is no need to refer to a text box as Txt*** in coding, just by its name. Please correct me if I am wrong. Should things like Position and Placing be referred to as TxtPosition or TxtPlacing in the Module?

Hope this helps to clarify things.
Best Regards
John

 

[dhookom]

Placing (this calls the Module)

What is the control source?

You haven't told us the result of this line of code:

Debug.Print "intCount: " & intCount

Also, check out my FAQ in this forum on debugging/trouble-shooting code. Try set a break point so you understand what you code is doing.

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Duane,

I think you missed the result which I sent you at 12.23 on the 23rd, in response to your suggestion. The debug print resulted in IntCount:3 which was the correct number of records.

As far as the control sourse of text box Placing is concerned, you have hit the nail on the head! I changed the name of the Count text box to txtRecordCount but forgot to change the name in the Placing text box control source. Now it works!!!

Having got it working, can I now get to the last bit of the nested problem?

If we look at the case of three records - I now need to insert another set of arguments based on the result shown in IntPosition Case 3. The arguments are as follows:

 Case 3
    If BooHO Then Placing_2 = "Actual Winner" Else Placing_2 = "Winner"
Case 2
    'If Case is 3 and Placing_2 = Actual Winner" then
    'If BooHO Then Placing_2 = "Actual Second Place" Else Placing_2 = "Winner"
    'If Case is 3 and Placing_2 = Winner" then
    'If BooHO Then Placing_2 = "Actual Second Place" Else Placing_2 = "Second Place"

My question is - In Case 2, how do I refer to "Case is 3" and "Placing_2 = " so that the code understands what I am asking for?
I hope this makes sense
Best Regards
John
Best Regards
John

 

[dhookom]

You hadn't provided the results of the debug.print from actually running the report. Your results of calling the function in the debug hid your real issue of getting the correct value from actually running the report. I think you would have "hit the nail on the head" if you had reviewed the debug.prints from the report.

Regarding the logic of the SELECT CASE, I would suggest you create a flow chart where decisions are made based on the argument values sent into the function. Then build you code based on the flow chart.



Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Thanks Duane, I'll investigate a flow chart. Might take a while so this thread will stay open a bit longer, I guess.
John

 

[rj51cxa]

Duane,

I've had a look at flow charts on the internet and I'm completely confused. This is a totally new idea for me so I wondered if you could point me at a good place to start learning.

Thanks a lot
John

 

[dhookom]

Start with a box near the top of a blank page. Write a question in it like "Is Honours Only". Then draw vertical lines from the bottom of the box for every possible answer. Use this to identify the decisions required to find the output based on the argument values. Your chart might look something like this. However you might want to start with the record count and finish with honours.

[tt]
+------------------------+
| Is Honours Only |
+------------------------+
| |
Yes No
| |
+----------------+ +----------------+
| Record Count | | Record Count |
+----------------+ +----------------+
| | | |
3 4 3 4
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[rj51cxa]

Thanks Duane, I'll play around with it.
I really appreciate your help.
Best Regards
John