Solved Using SELECT CASE with variables

[pooldead]

I have the following code and am using the really looking at the "mgrCheck"/"ownCheck" variables for my question. I put these in to assist with my Select Case statement.

            If DCount("*", "Table1") > 0 Then
                totalAcct = DCount("*", "Table1")
                mgrDeny = DCount("*", "Table1", "[APPROVE/DENY] Like 'Deny'")
                mgrTotal = DCount("*", "Table1", "Completed = 'Yes'")
                mgrPer = (mgrTotal / totalAcct)
                mgrCheck = 1
            Else
                mgrPer = 0
                mgrDeny = 0
                mgrCheck = 0
            End If

            If DCount("*", "Table2") > 0 Then
                totalAcct = DCount("*", "Table2")
                ownDeny = DCount("*", "Table2", "[APPROVE/DENY] Like 'Deny'")
                ownTotal = DCount("*", "Table2", "Completed = 'Yes'")
                ownPer = (ownTotal / totalAcct)
                ownCheck = 1
            Else
                ownPer = 0
                ownDeny = 0
                ownCheck = 0
            End If

My Select Case is meant to display 3 different msgbox's, depending on the combination of variables:

            Select Case mgrCheck
                Case (mgrCheck = 1) And (ownCheck = 0)               
                    MsgBox "# of Accounts Denied by Manager: " & Space(22) & mgrDeny & vbNewLine & _
                            "# of Accounts Denied by Owner: " & Space(26) & ownDeny & vbNewLine & _
                            "Total Completion Percentage: " & Space(20) & FormatPercent(mgrPer / ((totalAcct) * 2)), vbInformation, "KPIs"
                Case (mgrCheck = 1) And (ownCheck = 1)
                    MsgBox "# of Accounts Denied by Manager: " & Space(22) & mgrDeny & vbNewLine & _
                            "# of Accounts Denied by Owner: " & Space(26) & ownDeny & vbNewLine & _
                            "Total Completion Percentage: " & Space(20) & FormatPercent((mgrPer + ownPer) / 2), vbInformation, "KPIs"
                Case (mgrCheck = 0) And (ownCheck = 0)
                    MsgBox "# of Accounts Denied by Manager: " & Space(22) & mgrDeny & vbNewLine & _
                            "# of Accounts Denied by Owner: " & Space(26) & ownDeny & vbNewLine & _
                            "Total Completion Percentage: " & Space(20) & FormatPercent(0), vbInformation, "KPIs"
            End Select

I don't think I'm calling these variables correctly in the Case statements, because when I click my associated form button, nothing happens. The problem is, I've never done something like this before and cannot identify where the problem lies.

 

[plog]

Here's the documentation for Select Case:

MS Access: Case Statement

This MSAccess tutorial explains how to use the Access Case statement with syntax and examples. The Microsoft Access Case statement can only be used in VBA code. It has the functionality of an IF-THEN-ELSE statement.

www.techonthenet.com

In the Select you declare the variable you are going to do the comparisons with (mgrCheck). Then in each Case you do the comparsion with the assumption that it is on the varialbe in the Select.

You should not use a Select Case but If statements:

MS Access: IF-THEN-ELSE Statement

This MSAccess tutorial explains how to use the Access IF-THEN-ELSE statement with syntax and examples. The Microsoft Access IF-THEN-ELSE statement can only be used in VBA code.

www.techonthenet.com

Also, what about the 4th case (mgrCheck=0, owncheck=1)?

 

[pooldead]

Here's the documentation for Select Case:

MS Access: Case Statement

This MSAccess tutorial explains how to use the Access Case statement with syntax and examples. The Microsoft Access Case statement can only be used in VBA code. It has the functionality of an IF-THEN-ELSE statement.

www.techonthenet.com

In the Select you declare the variable you are going to do the comparisons with (mgrCheck). Then in each Case you do the comparsion with the assumption that it is on the varialbe in the Select.

You should not use a Select Case but If statements:

MS Access: IF-THEN-ELSE Statement

This MSAccess tutorial explains how to use the Access IF-THEN-ELSE statement with syntax and examples. The Microsoft Access IF-THEN-ELSE statement can only be used in VBA code.

www.techonthenet.com

Also, what about the 4th case (mgrCheck=0, owncheck=1)?

Thanks! I'll switch it over to If-Thens....I'm not sure why I didn't think to try that!

As for the 4th case, its a process that puts information into Table1, then proceeds to Table2 leaving Table1 filled (for documentation purposes) until the very end. I wasn't going to include it, but now that I think about it....I should probably to call out an error in the way the process was followed. Thanks for pointing it out!

 

[Gasman]

I would probably use a Case statment as well?

Sub TestCase1()
Dim iOne As Integer, iTwo As Integer

iOne = 1: iTwo = 2
iOne = 1: iTwo = 2
iOne = 1: iTwo = 2

Select Case iOne And iTwo
    Case 1 And 2
        MsgBox " 1 and 2"
    Case Else
        MsgBox " Not 1 and 2"
End Select
End Sub

HTH

 

[isladogs]

Or this...

   Select Case mgrCheck
      Case 1
         Select Case ownCheck
          Case 0              
             MsgBox "# of Accounts Denied by Manager: " & Space(22) & mgrDeny & vbNewLine & _
                    "# of Accounts Denied by Owner: " & Space(26) & ownDeny & vbNewLine & _
                    "Total Completion Percentage: " & Space(20) & FormatPercent(mgrPer / ((totalAcct) * 2)), vbInformation, "KPIs"
          Case 1
             MsgBox "# of Accounts Denied by Manager: " & Space(22) & mgrDeny & vbNewLine & _
                    "# of Accounts Denied by Owner: " & Space(26) & ownDeny & vbNewLine & _
                    "Total Completion Percentage: " & Space(20) & FormatPercent((mgrPer + ownPer) / 2), vbInformation, "KPIs"
          End Select
               
      Case 0
        Select Case ownCheck
          Case 0              
             MsgBox "# of Accounts Denied by Manager: " & Space(22) & mgrDeny & vbNewLine & _
                    "# of Accounts Denied by Owner: " & Space(26) & ownDeny & vbNewLine & _
                    "Total Completion Percentage: " & Space(20) & FormatPercent(0), vbInformation, "KPIs"
          Case 1
             'missing code to go here?
          End Select
      End Select

 

[The_Doc_Man]

It is going to come down to which one you like better. Nested case statements are perfectly fine and you don't have a lot of cases to consider so they should be easy to code. But using IF-ELSEIF-...-ELSE-END statements works just as well when the combinations you need to handle are limited in number. Sadly, Access doesn't show us the p-code it generates so we have no way to know which method is internally more efficient.