modGetFieldName - VBA help to get started (1 Viewer)

[CanadianAccessUser]

Hello!

I'm looking for a bit of code for a module that will return the field name from an expression (that uses an array) instead of returning the value in that field.

Example:
(Minimum is a working module that finds the smallest number in the array)
Field1 = 34
Field2 = 53
Field3 = 21
Field4 = 62

GetFieldName(Minimum([Field1],[Field2],[Field3],[Field4])) = "Field3"

I've searched but can't find a way to select the field name anywhere...
Any ideas would be appreciated

 

[pr2-eugin]

Can you please show the Code for Minimum function?

 

[CanadianAccessUser]

Function Minimum(ParamArray FieldArray() As Variant)
' Declare the local variables.
Dim I As Integer
Dim Smallest As Variant
 
' Set the variable Smallest equal to the array of values.
Smallest = FieldArray(0)
 
' Cycle through each value from the row to find the smallest.
 
For I = 0 To UBound(FieldArray)
If FieldArray(I) < Smallest Then
Smallest = FieldArray(I)
End If
Next I
 
' Return the minimum value found.
Minimum = Smallest
 
End Function

 

[gemma-the-husky]

you can find a name of a "control", but I doubt you can find the name of a variable.

As far compilers go, I think they just tokenise all variables, to refer to the allocated memory location - so it isn't directly available in memory as "field1" etc.

 

[CanadianAccessUser]

Dave,
Does that mean that access can't tell me what field the value came from?

Right now this:
LowestValue: Minimum([Field1],[Field2],[Field3],[Field4]) = 21
Isn't helpful at all without the name of the field. I can do something like the following in a separate query field:
IIf([LowestValue]=[Field1], "Field 1", IIf([LowestValue]=[Field2], "Field 2", IIf([LowestValue]=[Field3], "Field 3", "Field 4")))

But the issue is that I'm looking for the 5 lowest and sometimes the fields can contain the same values.
Example:
Field1=28
Field2=21
Field3=21
Field4=54
The system will tell me that 21 is the lowest. Then I run my Minimum2() code for the second lowest and that will say 21 is the second lowest. Perfect right? But it ends up looking like this:

LowestValue---LowestField---SecondLowestValue---SecondLowestField
----21------------Field2---------------21----------------Field2
--Correct---------Correct------------Correct------------Incorrect

This seems like such an easy thing for Access to be able to do... If I can't do this then how do I get the IIf function to overlook anything already placed in [LowestField] and find the next one?