Help with Functions to determine greatest of a set

[Ary]

Alright, I have eight fields in a record, all numbers. I need to build an
expression that will determine which one of these fields contains the
greatest value, then output one of 8 text strings to identify which field
contains the highest value.

I can use eight seperate Iif expressions, but this is rather unwieldy and
makes it harder to update in future.

Any suggestions would be welcome.

Thanks!

 

[Allen Browne]

See MaxOfList() in this article:
http://allenbrowne.com/func-09.html

 

[Ary]

Thank you, that appears to be exactly what I'm looking for. However, it gives
the output as the number that is contained in the field. Is there a way I can
get the output to be the name of the field which contains the greatest value?

Thanks,

 

[Allen Browne]

Right: that code doesn't name the field.

You could re-write it so that you place the field names in an array, and
store the index number of the greatest value, which then lets you retrieve
the field name from the array.

 

[Ary]

Therein lies the problem. I have really no familiarity with Visual Basic
whatsoever. I've been poking at it all night trying different ways to get
those field names as the array, using the Help and an Access for Dummies
guide, but I still don't have any idea what I'm doing.

I guess my next question is: How do I create an array?
--
-Ary

Right: that code doesn't name the field.

You could re-write it so that you place the field names in an array, and
store the index number of the greatest value, which then lets you retrieve
the field name from the array.

 

[Allen Browne]

Hmm. Teaching you VBA, variables, arrays, bounds, loops, branches, etc is
beyond the scope of a newsgoup post.

Time to enroll in a course, or get some Access books, or find a user group,
or whatever is best for the way you learn.

 

[Gary Walter]

Here be a quick-and-dirty adaptation
of Allen's function (not fully tested)
to return the field name of the field
in the record with the max value:

Public Function MaxOfListFieldName(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.
Dim varFldNm As Variant 'current field name
Dim varMaxFldNm As Variant 'field name with largest value found so far

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
'assumes LBound = 0
Select Case i Mod 2
Case 0 'fieldname
varFldNm = varValues(i)
Case 1 'field value
If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
varMaxFldNm = varFldNm
End If
End If

End Select
Next

MaxOfListFieldName = varMaxFldNm

End Function

Feed it the fieldname in quotes followed by the field

tbl1

ID f1 f2 f3
1 1 2 3
2 2 3 1
3 3 2 1

SELECT
tbl1.ID,
tbl1.f1,
tbl1.f2,
tbl1.f3,
MaxOfListFieldName("f1",[f1],"f2",[f2],"f3",[f3]) AS MaxField
FROM tbl1;

result:

ID f1 f2 f3 MaxField
1 1 2 3 f3
2 2 3 1 f2
3 3 2 1 f1

Therein lies the problem. I have really no familiarity with Visual Basic
whatsoever. I've been poking at it all night trying different ways to get
those field names as the array, using the Help and an Access for Dummies
guide, but I still don't have any idea what I'm doing.

I guess my next question is: How do I create an array?

 

[Ary]

Thanks!

I've put together my own version as well, also untested, that I'll try
first, but if that doesn't work I'll give this a try next. It looks like your
version will be a little more universal than mine, as in I could use exactly
the same function for any set of fieldnames, whereas my solution is
restricted for use with just this particular case.

Either way, I'll test it out and let you know how it works!
--
-Ary

Here be a quick-and-dirty adaptation
of Allen's function (not fully tested)
to return the field name of the field
in the record with the max value:

Public Function MaxOfListFieldName(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.
Dim varFldNm As Variant 'current field name
Dim varMaxFldNm As Variant 'field name with largest value found so far

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
'assumes LBound = 0
Select Case i Mod 2
Case 0 'fieldname
varFldNm = varValues(i)
Case 1 'field value
If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
varMaxFldNm = varFldNm
End If
End If

End Select
Next

MaxOfListFieldName = varMaxFldNm

End Function

Feed it the fieldname in quotes followed by the field

tbl1

ID f1 f2 f3
1 1 2 3
2 2 3 1
3 3 2 1

SELECT
tbl1.ID,
tbl1.f1,
tbl1.f2,
tbl1.f3,
MaxOfListFieldName("f1",[f1],"f2",[f2],"f3",[f3]) AS MaxField
FROM tbl1;

result:

ID f1 f2 f3 MaxField
1 1 2 3 f3
2 2 3 1 f2
3 3 2 1 f1

 

[Ary]

OK, just for your information and anyone else that this might help, this is
what I came up with. It doesn't give me the field name, but it gives me the
number of which iteration produced the largest value, and I can index that in
a seperate table:

Function MaxOfList(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.
Dim varIndex As Integer 'Which iteration produces the largest value.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
varIndex = i
End If
End If
Next

MaxOfList = varIndex
End Function