Help! Need a VBA code to return field name rather than value.

[geeks]

Hello,

I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.

Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant


Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer

ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0

' Transfer the non-Null values to a temporary array
For I = 0 To UBound(FieldArray)
If IsNull(FieldArray(I)) = False Then
varTempArray(intArrayValues) = FieldArray(I)
intArrayValues = intArrayValues + 1
End If
Next I

If intArrayValues > 1 Then
' Sort the temporary array, lowest to highest (Bubble sort)
For I = 0 To intArrayValues - 2
For J = I + 1 To intArrayValues - 1
If varTempArray(J) < varTempArray(I) Then
varTempValue = varTempArray(J)
varTempArray(J) = varTempArray(I)
varTempArray(I) = varTempValue
End If
Next J
Next I

' Remove duplicate values
I = 0
While I < intArrayValues - 2
If varTempArray(I) = varTempArray(I + 1) Then
For J = I To intArrayValues - 1
varTempArray(J) = varTempArray(J + 1)
Next J
intArrayValues = intArrayValues - 1
End If
I = I + 1
Wend
End If


If intPosition <= intArrayValues Then
NthMinimum = varTempArray(intPosition - 1)
Else
' The requested position is higher than the number of values in the array
NthMinimum = Null
End If


End Function

As you can see, this works to find these values while ignoring NULLS. If anyone has any suggestions on how to return the field name, I would greatly appreciate it!

Thanks in advance!

 

[pr2-eugin]

For some reason I have this code ! Lol.

Public Function GetFieldName(fieldList As String, theTableName As String, criteria As String) As String
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim strSQL As String, rsO As DAO.Recordset
    Dim tmpArr, valArr, passArr
    Dim iCtr As Long, minVal
        
    tmpArr = Split(fieldList, ",")
    valArr = Split(fieldList, ",")
    
    Set rsO = CurrentDb.OpenRecordset("SELECT " & fieldList & " FROM " & theTableName & " WHERE " & criteria)
    
    For iCtr = 0 To UBound(tmpArr)
        passArr = passArr & rsO.Fields(iCtr) & ","
        valArr(iCtr) = rsO.Fields(iCtr)
    Next
    
    minVal = Minimum(valArr)

    For iCtr = 0 To UBound(valArr)
        If minVal = valArr(iCtr) Then
            GetFieldName = Trim(tmpArr(iCtr))
            Exit Function
        End If
    Next
End Function

Function Minimum(ParamArray FieldArray() As Variant)
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
[COLOR=Green]    ' Declare the two local variables.[/COLOR]
    Dim i As Integer
    Dim Smallest As Variant
     
[COLOR=Green]    ' Set the variable currentVal equal to the array of values.[/COLOR]
    Smallest = FieldArray(0)
     
[COLOR=Green]    ' Cycle through each value from the row to find the smallest.[/COLOR]
     
    For i = 0 To UBound(FieldArray)
        If Smallest > FieldArray(i) Then
            Smallest = FieldArray(i)
        End If
    Next i
     
[COLOR=Green]    ' Return the minimum value found.[/COLOR]
    Minimum = Smallest
End Function

I don't even use this in my application.