I have values stored in different fields: field1, field2, field3, ...
Another field finds the max field: max (field1, field2, field3, ...)
How do I get the field name of the max field (e.g., field2)?
Since the fields are already known, construct a Case statement that returns the field name.
maxvalue = Max( field1, field2, field3 ) ;
field1 = maxvalue ; GetFieldName( field1 ) ;
field2 = maxvalue ; GetFieldName( field2 ) ;
field3 = maxvalue ; GetFieldName( field3 )
Things get interesting if more than one field has the maximum value: this statement returns the first in the order the expressions are evaluated.
This would be a LOT easier if related records were used instead of separate fields, particularly if there are a lot of fields.
Retrieving data ...