How to find recordset with highest field value using MAX function where field is a string?-CodePudding

I have a table in Microsoft Access with multiple repeated records, containing different values in a specific column field. Due to original design, the column field type is a Short Text, whose values contains all numbers, except a "No Number" selection, and cannot be changed for the time being.

In VBA, I'm trying to find the record containing the highest value in Field3. For example, the table might look like this:

Field1 | Field2 | Field3
Jay        Red      2
Aden       Blue     1
Jay        Red      5
Colin      Orange   3
Jay        Red    No Number

I would like to return the recordset containing the row containing Jay, Red, 5.

I wrote an SQL string, but do to my limited knowledge, it's not right because Access complains about the following:

Here's what the SQL string looks:

mySQL = "SELECT " & _
            "(SELECT TOP 1 Field3" & _
             "FROM MyTable" & _
             "Where Field1 = '" & rs1.Fields("Field1") & "'" & _
             ") as Field3, Field1, Field2 " & _
             "FROM " & _
             "(SELECT Max(Val(Field3)) As Fld3, Field1, Field2 " & _
             "FROM MyTable" & _
             "GROUP BY Field3, Field1, Field2)"
                        
            criteria = "Field1= '" & rs1.Fields("Field1") & "'"

Set rs2 = CurrentDb.OpenRecordset(mySQL, _
                                 dbOpenSnapshot, _
                                 dbReadOnly)

With rs2
    rs2.FindLast criteria 
                
    If Not rs2.NoMatch Then
        Dim str1 As String
                    
        str1 = rs2.Fields("Field3")

        rs2.Close
        Set rs2 = Nothing        
End With

I dont think the SQL string is right in general, but I'm also not sure how to use the MAX function in the cause where the column field is a string? Any help is appreciated.

CodePudding user response：

You can achieve this quite simply with an SQL that looks like this:

SELECT TOP 1 Field1, Field2, Field3
FROM MyTable
WHERE Field3 <> 'No Number'
ORDER BY CInt(Field3) DESC;

This assumes that the only non-numeric value is 'No Number'.

Combining TOP 1 with ORDER BY DESC has the same effect of getting the maximum value, but is simpler, because there is no need for a sub-select. If you need to add other criteria, just 'AND' them to the WHERE clause.