Code to get Field name

[geeks]

Hello,

I have a code that finds the lowest number in a row across the fields. Now, I am trying to find a code to look at the lowest value, 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 the lowest value while ignoring NULLS. If anyone has any suggestions on how to return the field name, I would greatly appreciate it!

Thanks in advance!

[June7]

Does FieldArray have the field names? Make TempArray 2-dimensional and save the field names into TempArray as well as field values.

[John_G]

I have a problem with your function. You want to find the minimum value in the array and its position? If that is the case, I don't think it works. Firstly, you are sorting the non-null values, which removes any indication of where they were in the array, and secondly, you are asking for a value in a specific position in the sorted array (intPosition), and unless intPosition=0, you are not getting the minimum.

You don't need the sort - you can scan the array and get the minimum and the position on one pass.

Something like this (untested):

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


Dim  varTempValue As Variant, intArrayValues As Integer
Dim I As Integer
Dim CurrentMinimum as Variant, CurrentPosition as variant

intArrayValues = 0
'
' Initialize CurrentMinimum to some "large" value, more than the largest possible value in the array
'
CurrentMinimum = 99999
CurrentPosition = 9999

For i = 0 to ubound(fieldarray)
  '
  '   If this array member is smaller that the current values, reset the values
  '
  if not IsNull(FieldArray(I)) then
    if fieldarray(i) < currentminimum then
      currentminimum = fieldarray(i)
      currentposition = i
    endif
  endif
next i
'
' At this point, the CurrentMinimum will be the minimum value in the array,
' and CurrentPosition will be where it was found (0 based)
'
nthMinimum = CurrentMinimum
intPosition = CurrentPosition

End Function

This function does what you say you want it to do, which is to find the minimum value in the array, and its position.

I don't know what you were doing with intPosition, so I used it to return the position of the minimum value.

The function returns the position of the first occurance of duplicate minimum values; if you want to return the second (or last) one, use if fieldarray(i) <= currentminimum then

I hope this helps.

John

[ranman256]

'No need to use arrays
Public Sub Scan()
Dim rst As Recordset
Dim fld As Field
Dim vLowest
Dim sFldName As String
Const kSTART = 99999
Set rst = CurrentDb.OpenRecordset(QryName)
vLowest = kSTART

With rst
While Not .EOF
For Each fld In rst.Fields

If fld.Value < vLowest Then
sFldName = fld.Name
vLowest = fld.Value
End If

'do something w lowest field
'here

Next
vLowest = kSTART
.movenext
Wend
End With
End Sub