asked on

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

Joe Howard

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 Schaefer

ASKER

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

Joe Howard

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

Karen Schaefer

ASKER

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.

Joe Howard

Can you please upload a sample db?

Karen Schaefer

ASKER

Here you goSample.mdb

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

Thanks for the input, however, this project was cancelled I no longer need assistance with this issue.