VBA "Select Case" Troubles

[Dalagrath]

Hello again! I am currently working on a code that discovers an items weight and uses a conversion method to convert that into a specific type. Now the big issue here is that in my code I have a Select Case for the weight (since there are three columns available for the weight) but it keeps defaulting to the Case Else even though there is clearly other Case matches above it. Here is my code:

 
Private Sub AnalysisUNITS_AfterUpdate()
    
    Dim resultsBegin As Integer
    Dim resultsFinish As Integer
    Dim calculationGramLiter As Integer
    Dim calculationWeightPerc As Integer
    Dim finalSG As Integer
    Dim InvNum As Integer
    
    'Transferring Data from form onto VB code
    [resultsBegin] = [AnalysisResults]
    [InvNum] = 999
    
    Select Case [SampleSG]
        Case SampleSG.Value > 0
            [finalSG] = SampleSG.Value
        Case SampleSG_SP.Value > 0
            [finalSG] = SampleSG_SP.Value
        Case SampleSG_LAB.Value > 0
            [finalSG] = SampleSG_LAB.Value
        Case Else
            MsgBox ("Please Enter a SG before continuing.")
                [Results] = [InvNum]
    End Select
    
    Select Case [AnalysisUNITS]
        Case [AnalysisUNITS] < 0
            MsgBox ("Please Enter the Analysis Units of Measure.")
            [Results] = [InvNum]
        Case [AnalysisUNITS] = 4
            [calculationWeightPerc] = [resultsBegin]
        Case [AnalysisUNITS] = 1
            [calculationGramLiter] = [resultsBegin]
            [calculationWeightPerc] = [calculationGramLiter] / [finalSG] / 10
        Case [AnalysisUNITS] = 2, 6, 8
            [calculationGramLiter] = [resultsBegin] * 1000
            [calculationWeightPerc] = [calculationGramLiter] / [finalSG] / 10
        Case [AnalysisUNITS] = 7
            [calculationGramLiter] = [resultsBegin] * 1000000
            [calculationWeightPerc] = [calculationGramLiter] / [finalSG] / 10
        Case [AnalysisUNITS] = 56, 68
            [calculationGramLiter] = [resultsBegin] * 0.001
            [calculationWeightPerc] = [calculationGramLiter] / [finalSG] / 10
        Case Else
            'Do Nothing
    End Select
    
    Select Case [calculationWeightPerc]
        Case [calculationWeightPerc] < 0
            [resultsFinish] = [InvNum]
        Case [calculationWeightPerc] > 100
            [resultsFinish] = [InvNum]
        Case Else
            [resultsFinish] = [calculationWeightPerc]
    End Select
    
    [Results] = [resultsFinish]
    'MsgBox ("The Criteria is " & Criteria & " the Value is " & AnalysisUNITS.Value)
End Sub

[June7]

Don't repeat the input in a Case structure:

Case Is < 0
...
Case 4
...
Case 1
...
Case 2, 6, 8
...
Case 7
...
Case 56, 68
...
Case Else

[Dalagrath]

Thanks for the quick reply and help with the bottom coding.

However, the main issue is with this segment here:

 
    Select Case [SampleSG]
        Case SampleSG.Value > 0
            [finalSG] = SampleSG.Value
        Case SampleSG_SP.Value > 0
            [finalSG] = SampleSG_SP.Value
        Case SampleSG_LAB.Value > 0
            [finalSG] = SampleSG_LAB.Value
        Case Else
            MsgBox ("Please Enter a SG before continuing.")
                [Results] = [InvNum]
    End Select

[June7]

Don't think Case can be used for this. You are changing the input in each case. Use If Then Else instead.

[Dalagrath]

As I read it, I thought that Case only went until it found the correct Case conditons and stopped at that point? Or I might be mistaken.

[June7]

Goes until it finds condition that meets the input of the Select Case line.

Select Case replaces:

If [AnalysisUNITS] < 0 Then
'do this
ElseIf [AnalysisUNITS] = 1 Then
'do this
ElseIf [AnalysisUNITS] = 2 Then
'do this
Else
'do nothing
End If

You want to do:

If [SampleSG] > 0 Then
'do this
ElseIf [SampleSG_SP] > 0 Then
'do this
ElseIf [SampleSG_LAB] > 0 Then
'do this
Else
'do this
End If

[Dalagrath]

I replaced my code with the following (per your instructions) and still got the same thing.

 
    If [SampleSG] > 0 Then
     [finalSG] = [SampleSG]
    ElseIf [SampleSG_SP] > 0 Then
        [finalSG] = [SampleSG_SP]
    ElseIf [SampleSG_LAB] > 0 Then
        [finalSG] = [SampleSG_LAB]
    Else
        MsgBox ("Please Enter a SG before continuing.")
        [Results] = [InvNum]
    End If

[June7]

Step debug. Set a breakpoint on the If Then line. Follow the code one line at a time as it executes. Are the values from form getting read by the code?

[Dalagrath]

I literally cannot understand this issue. When I break part of the line, part of the Case Structure for the specific calculations works...

[June7]

Do not understand 'break part of the line'.

Do you want to make project available for analysis? Attach to post, zip if large, run Compact & Repair first.

[Dalagrath]

No I understand the breaking and debugging, and now i discovered more errors. Let me mess with it some more and see if I can get the answer. thank you though.

[Dalagrath]

Through some debugging, I realized that the main issue is with this section:

 
    Select Case [AnalysisUNITS]
        Case [AnalysisUNITS] = 4
            [calculationWeightPerc] = [resultsBegin]
        Case [AnalysisUNITS] = 1
            [calculationGramLiter] = [resultsBegin]
            [calculationWeightPerc] = [calculationGramLiter] / [finalSG] / 10
        Case [AnalysisUNITS] = 2, 6, 9
            [calculationGramLiter] = [resultsBegin] * 1000
            [calculationWeightPerc] = [calculationGramLiter] / [finalSG] / 10
        Case [AnalysisUNITS] = 7
            [calculationGramLiter] = [resultsBegin] * 1000000
            [calculationWeightPerc] = [calculationGramLiter] / [finalSG] / 10
        Case [AnalysisUNITS] = 56, 68
            [calculationGramLiter] = [resultsBegin] / 1000
            [calculationWeightPerc] = [calculationGramLiter] / [finalSG] / 10
    End Select

This is weird because the 2, 6, 9 Case acts funny as well. The 6 and the 9 work perfect, while the 2 just plain ignores the executable code.

In fact only Case 6 and 9 work...

[drexasaurus]

I notice you are using Windows 7 and Access 2010. Are you creating the program on a Windows 7 machine and running it on an earlier OS, or did you create the file originally on a different OS than you are currently developing this file on? I ask because I had trouble with Select statements and certain types of string comparisons in this scenario. Decompiling and repairing on the oldest OS fixes this problem for me.

Also, if you suspect that the problem is not with the code and it compiles, you may want to decompile the code and try it again. It usually turns out that I am doing something wrong, but every once in a while this fixes a problem for me.

[Dalagrath]

Thnaks for the tips, I will try those and get back to you.

[AndreT]

Try this -

Select Case True
case a>0
...
Case a=1
Case b=5
case x=y
case else
end select

the first condition evaluated to be true will be executed.