determine minimum value with a function

I need to loop thru a recordset to determine the minimum value of a text(High, Medium, Low) field, by assigning a number to the text value, then Display which is the minimum value per the group of recordsets.

Public Function GetgConfidence() As String
Dim nConfLvl As Integer
Dim curDB As Database
Dim strSQL As String
Dim rs As Recordset

Set curDB = CurrentDb()

strSQL = "Select ConfidenceLevel from tblTC Where CityRecID =193" ' " & gcityID & ""
Set rs = curDB.OpenRecordset(strSQL)
rs.MoveFirst
Do Until rs.EOF
    Select Case rs.Fields("ConfidenceLevel").Value
        Case "High"
            nConfLvl = 3
        Case "medium"
            nConfLvl = 2
        Case "Low"
            nConfLvl = 1
        Case Else
            Exit Function
        End Select
     '   if nConfLvl
     rs.MoveNext
Loop


End Function

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Is this what you want:
Public Function GetgConfidence() As String

    Dim nConfLvl As Integer
    Dim curDB As Database
    Dim strSQL As String
    Dim lngTemp As Long
    Dim rs As Recordset

    Set curDB = CurrentDb()
    lngTemp = 1

    strSQL = "Select ConfidenceLevel from tblTC Where CityRecID =193"    ' " & gcityID & ""
    Set rs = curDB.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        Select Case rs.Fields("ConfidenceLevel").Value
            Case "High"
                nConfLvl = 3
            Case "medium"
                nConfLvl = 2
            Case "Low"
                nConfLvl = 1
            Case Else
                Exit Function
        End Select
        If nConfLvl < lngTemp Then lngTemp = nConfLvl
        MsgBox lngTemp
        rs.MoveNext
    Loop

End Function

Open in new window

Karen SchaeferBI ANALYSTAuthor Commented:
thanks that helped except I need to determine the lowest level and then revert the value back to a text (High, Med, Low)

here is what I have:
Public Function GetgConfidence() As String

    Dim nConfLvl As Integer
    Dim curDB As Database
    Dim strSQL As String, strSQL1 As String
    Dim lngTemp As Long
    Dim rs As Recordset, rs1 As Recordset

    Set curDB = CurrentDb()
    lngTemp = 1
    strSQL1 = "Select cityrecid from tbltc"
    
    Set rs1 = curDB.OpenRecordset(strSQL1)
        rs1.MoveFirst
        Do Until rs1.EOF
            gCityRecID = rs1.Fields("cityRecid")
                strSQL = "Select ConfidenceLevel from tblTC Where CityRecID = " & gCityRecID & ""
                Set rs = curDB.OpenRecordset(strSQL)
                rs.MoveFirst
                Do Until rs.EOF
                    Select Case rs.Fields("ConfidenceLevel").Value
                        Case "High"
                            nConfLvl = 1
                        Case "medium"
                            nConfLvl = 2
                        Case "Low"
                            nConfLvl = 3
                        Case Else
                            Exit Function
                    End Select
                    If nConfLvl < lngTemp Then lngTemp = nConfLvl
                        Select Case nConfLvl
                            Case 1
                                GetgConfidence = "High"
                            Case 2
                                GetgConfidence = "Medium"
                            Case 3
                                GetgConfidence = "Low"
                           
                        End Select
                    rs.MoveNext
                Loop
            rs1.MoveNext
        Loop
 
End Function

Open in new window


Still returning the same value for all records:  results should be

Cancun High
Madrid Med
Ajax   -  Low
MacroShadowCommented:
Does this give you the requested information:
Public Function GetgConfidence() As String

    Dim nConfLvl As Integer
    Dim curDB As Database
    Dim strSQL As String, strSQL1 As String
    Dim lngTemp As Long
    Dim rs As Recordset, rs1 As Recordset

    Set curDB = CurrentDb()
    lngTemp = 1
    strSQL1 = "Select cityrecid from tbltc"

    Set rs1 = curDB.OpenRecordset(strSQL1)
    rs1.MoveFirst
    Do Until rs1.EOF
        gCityRecID = rs1.Fields("cityRecid")
        strSQL = "Select ConfidenceLevel from tblTC Where CityRecID = " & gCityRecID & ""
        Set rs = curDB.OpenRecordset(strSQL)
        rs.MoveFirst
        Do Until rs.EOF
            Select Case rs.Fields("ConfidenceLevel").Value
                Case "High"
                    nConfLvl = 1
                Case "medium"
                    nConfLvl = 2
                Case "Low"
                    nConfLvl = 3
                Case Else
                    Exit Function
            End Select
            If nConfLvl < lngTemp Then lngTemp = nConfLvl
            Select Case nConfLvl
                Case 1
                    GetgConfidence = "High"
                Case 2
                    GetgConfidence = "Medium"
                Case 3
                    GetgConfidence = "Low"
            End Select
            MsgBox gCityRecID & ": " & GetgConfidence
            rs.MoveNext
        Loop
        rs1.MoveNext
    Loop

End Function

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the input, however,  Step 1 need to determine for multiple records for the same city the confidence level per city.  Confidence level will be the lowest  value per city.  ie.  Ajax (5 records) of those 5 one is at low, so Ajax 's confidence level- LOW, so once low has been established for Ajax, move to next city and repeat process.

Need to play more with the outer loop to move to next record once 1 of confidencelevel = low.

Public Function GetgConfidence() As String

    Dim nConfLvl As Integer
    Dim curDB As Database
    Dim strSQL As String, strSQL1 As String
    Dim lngTemp As Long
    Dim rs As Recordset, rs1 As Recordset

    Set curDB = CurrentDb()
    lngTemp = 1
    strSQL1 = "Select cityrecid from tbltc"

    Set rs1 = curDB.OpenRecordset(strSQL1)
        rs1.MoveFirst
        Do Until rs1.EOF
            gCityRecID = rs1.Fields("cityRecid")
                strSQL = "Select ConfidenceLevel from tblTC Where CityRecID = " & gCityRecID & ""
                Set rs = curDB.OpenRecordset(strSQL)
                rs.MoveFirst
                Do Until rs.EOF
                    Select Case rs.Fields("ConfidenceLevel").Value
                        Case "High"
                            nConfLvl = 1
                        Case "medium"
                            nConfLvl = 2
                        Case "Low"
                            nConfLvl = 3
                        Case Else
                            Exit Function
                    End Select
                    If nConfLvl = 3 Then
                          GetgConfidence = "Low"
                          GoTo cont:
                    ElseIf nConfLvl < lngTemp Then lngTemp = nConfLvl
                        Select Case nConfLvl
                            Case 1
                                GetgConfidence = "High"
                            Case 2
                                GetgConfidence = "Medium"
                        End Select
                    End If
                   rs.MoveNext
               Loop
cont:             rs1.MoveNext
        Loop

End Function

Open in new window


still not returning the expected results - keeps looping thru current rec id, instead of moving on the next CityRecId.
MacroShadowCommented:
Can you please upload a sample db?
Karen SchaeferBI ANALYSTAuthor Commented:
Here you goSample.mdb
MacroShadowCommented:
If I understood what you want this does it:
Public Function GetgConfidence() As String

    Dim nConfLvl As Integer
    Dim curDB As Database
    Dim strSQL As String, strSQL1 As String
    Dim lngTemp As Long
    Dim rs As Recordset, rs1 As Recordset
        
    Set curDB = CurrentDb()
    lngTemp = 1
    strSQL1 = "Select cityrecid from tblSample"

    Set rs1 = curDB.OpenRecordset(strSQL1)
    rs1.MoveFirst
    Do Until rs1.EOF
        gCityRecID = rs1.Fields("cityRecid")
        strSQL = "Select ConfidenceLevel from tblSample Where CityRecID = " & gCityRecID & ""
        Set rs = curDB.OpenRecordset(strSQL)
        rs.MoveFirst
        Do Until rs.EOF
            Select Case rs.Fields("ConfidenceLevel").Value
                Case "High"
                    nConfLvl = 1
                Case "medium"
                    nConfLvl = 2
                Case "Low"
                    nConfLvl = 3
                Case Else
                    Exit Function
            End Select
            If nConfLvl < nConfLvl Then nConfLvl = nConfLvl
            Select Case nConfLvl
                Case 1
                    GetgConfidence = "High"
                Case 2
                    GetgConfidence = "Medium"
                Case 3
                    GetgConfidence = "Low"
            End Select

            rs.MoveNext
        Loop
        Debug.Print gCityRecID & ": " & GetgConfidence
        rs1.MoveNext
    Loop

End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the input, however, this project was cancelled I no longer need assistance with this issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.