Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2014

    Unanswered: Code to get Field name


    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
    End If

    If intPosition <= intArrayValues Then
    NthMinimum = varTempArray(intPosition - 1)
    ' 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!

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 23
    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 =
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts