how to retrieve the column name

[rlaplume]

I have an Access database with a table that contains 11 fields. The first is a unique ID and the other 10 fields are various numbers. I need to know which of the 10 columns contains the highest number for each row and return the 2 rightmost characters of that column name. I started with the “maximum” function that I found on-line and it works to give me the maximum number in each row, but I need it modified to return the 2 rightmost characters of the column name that contains that number. I’m sure it’s a simple thing, if only I knew what I was doing!

Function Maximum(ParamArray FieldArray() As Variant)
 ' Declare the two local variables.
 Dim I As Integer
 Dim currentVal As Variant
 ' Set the variable currentVal equal to the array of values.
 currentVal = FieldArray(0)
 ' Cycle through each value from the row to find the largest.
 For I = 0 To UBound(FieldArray)
 If FieldArray(I) > currentVal Then
 currentVal = FieldArray(I)
 End If
 Next I
 ' Return the maximum value found.
 Maximum = currentVal
 End Function

[June7]

Looks like the function is receiving an array that is populated in another procedure. Unless that array has an element that holds the fieldnames, don't see how you can capture the name in that function.

The function passes back field value. What do you need the value for? Instead of passing back the value, can pass back the array index and possibly use that in the calling procedure to identify the associated field.

[rlaplume]

I only started with this function because I knew I would need to find the row with the highest value in the row anyway. I don't actually need to know the value, I just need to know the column name (or more specifically, the last 2 letters in the column name) so I guess that could be derived from the array index value. Can you show me how I would have a function return that?

[June7]

Change the function you have to capture and return the array index that has the maximum value. Use another variable in the loop.

currentVal = FieldArray(I)
maxIndex = I

Then return the maxIndex:

Maximum = maxIndex