# Thread: Code to get Field name

1. Registered User
Join Date
Apr 2014
Posts
1

## Unanswered: Code to get Field name

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!

2. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
630
Code:
```dim fld as field
dim rst as
dim vLow, vFld

vLow = 9999999
set rst = openrecordset(query)
'scan 1st record
for each fld in rst.fields
if fld.value < 9999999 then
vLow = fld.value
vFld = fld.name
endif
end if```
you shouldnt be searching across fields for values, you should search a single field. (your table is designed wrong) Always search values down, not across.