Select Case

[belsha]

Can someone show me the correct way to change the If Then statement below to a Select Case?

Function CalcPreSpeed() 'Calculate the Pre speed using the formula (sum/57.5)*100
Dim iQ3A As Integer
Dim iQ3B As Integer
Dim iQ3C As Integer
Dim iQ3D As Integer

iQ3A = Forms!frmMain!PreQ_3A
iQ3B = Forms!frmMain!PreQ_3B
iQ3C = Forms!frmMain!PreQ_3C
iQ3D = Forms!frmMain!PreQ_3D

If iQ3A <> 6 And iQ3B <> 6 And iQ3C <> 6 And iQ3D = 6 Then
Forms!frmMain.PreSpeed = (((iQ3A * 1.5) + (iQ3B * 2) + (iQ3C * 3)) / 32.5) * 100
ElseIf iQ3A <> 6 And iQ3B <> 6 And iQ3C = 6 And iQ3D = 6 Then
Forms!frmMain.PreSpeed = (((iQ3A * 1.5) + (iQ3B * 2)) / 17.5) * 100
ElseIf iQ3A = 6 And iQ3B = 6 And iQ3C = 6 Or iQ3A = 6 And iQ3C = 6 And iQ3D = 6 Or iQ3B = 6 And iQ3C = 6 And iQ3D = 6 Then 'If 3 answers in the section are 6, do not calculate the total score for that section
Forms!frmMain.PreSpeed = 0
Else
Forms!frmMain.PreSpeed = (((iQ3A * 1.5) + (iQ3B * 2) + (iQ3C * 3) + (iQ3D * 5)) / 57.5) * 100
End If
End Function

 

[Grommet]

I don't belive there is a correct way. Select case is usually used to check one value, if you do use case select you will still need your If statments.

Select case may tidy it up a bit, but I think its ok as is. FYI this is the Case Select example from the help:

Dim Number
Number = 8    ' Initialize variable.
Select Case Number    ' Evaluate Number.
Case 1 To 5    ' Number between 1 and 5, inclusive.
    Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8    ' Number between 6 and 8.
    Debug.Print "Between 6 and 8"
Case 9 To 10    ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else    ' Other values.
    Debug.Print "Not between 1 and 10"
End Select

 

[belsha]

Thanks, it works, but I was looking for a "neater" way to handle it should I have to do something like it again.

 

[Mile-O]

Break it into small private functions to make it easier to manage.

 

[The_Doc_Man]

If what you really care about is the NUMBER of items that are 6 and not which ones are 6, then

Dim loSixes As Long

...

loSixes = 0
If IQ3A = 6 Then loSixes = loSixes + 1
If IQ3B = 6 Then loSixes = loSixes + 1
If IQ3C = 6 Then loSixes = loSixes + 1
If IQ3D = 6 Then loSixes = loSixes + 1

Select Case loSixes
  Case 0
      ...{whatever}
  Case 1
      ...{whatever}
  Case 2
      ...{etc.}
  Case 3
      ...{and so on}
  Case 4
      ...{and so forth}
End Select

Now, if it matters as to WHICH ONE is equal to 6, this gets more complex.

 

[belsha]

It does. The sixes mean "can't do it". The questions are like:
1a Walk 50 feet, 1b Walk 100 feet, 1c Walk 150 feet. So, What happens is if 1abc get (eg.) 1's , do the whole calculation. If 1bc get 6, Don't calculate, =0. If 1a = 1 and 1b=1 but 1c=6, then do a different calculation, which takes the value of 1a and 1b x the weight for each question, divides by a different denominator (because we will not be dealing with the weighted formula for 1c if it is 6) and then multiplies by 100. The assumption is you are not going to say you can't walk 50 foot but can walk 100 (and I have cleanup queries to also recheck and show any irregularities.)

 

[Pat Hartman]

I doubt that the following condition is being properly evaluated. When you use AND and OR in a complex condition, you need to use parentheses to tell Access how to evaluate the statement. This is the same problem that you would have if you combined multiplaction/division and addition/subtraction in a complex calculation. You need parentheses to specify the order of presendence.

ElseIf iQ3A = 6 And iQ3B = 6 And iQ3C = 6 Or iQ3A = 6 And iQ3C = 6 And iQ3D = 6 Or iQ3B = 6 And iQ3C = 6 And iQ3D = 6 Then 'If 3

should possibly be:

ElseIf (iQ3A = 6 And iQ3B = 6 And iQ3C = 6) Or (iQ3A = 6 And iQ3C = 6 And iQ3D = 6) Or (iQ3B = 6 And iQ3C = 6 And iQ3D = 6) Then 'If 3

The statement is actually three sets of AND conditions any one of which can be true so they are connected with OR.

 

[raskew]

Think this might work. When testing from the debug window, it'd look like:
iq3a = 4
iq3b = 5
iq3c = 6
iq3d = 6
? prespeed(iq3a, iq3b, iq3c, iq3d)

Function Prespeed(ParamArray varNums() As Variant) As Variant
Dim subtot As Integer, i As Integer, maxCount As Integer

Debug.Print UBound(varNums)
For i = 0 To UBound(varNums)
   maxCount = maxCount + (-1 * (varNums(i) = 6))
   subtot = subtot + varNums(i)
Next i
Select Case maxCount
   Case 1 And varNums(3) = 6  
      Prespeed = (((varNums(0) * 1.5) + (varNums(1) * 2) + (varNums(2) * 3)) / 32.5) * 100
   Case 2 And varNums(2) + varNums(3) = 12
      Prespeed = (((varNums(0) * 1.5) + (varNums(1) * 2)) / 17.5) * 100
   Case 3
      Prespeed = 0
   Case Else
      Prespeed = (((varNums(0) * 1.5) + (varNums(1) * 2) + (varNums(2) * 3) + (varNums(3) * 5)) / 57.5) * 100
End Select
End Function

Test it by varying the values of iq3a - 1q3D.

HTH - Bob

 

[belsha]

Pat, I used your suggestion and used the parantheses, everything is working fine and is more readable. raskew, when I tried your suggestion I got subscript out of range, and was taken to the Case 1 line of code. Thank you both for your help.