return field name

[maryjo2001]

does anyone know how to return a field name from a table? for example table1 had field1, field2, and field3. i have created a function that returns the maximum value of the three fields. what i want it to do now is to tell me the field name of that field with the maximum value.

 

[MichaelRed]

You need to START with the fields collection of the tabledef. Form there, you can retrieve both the VALUE of the field and the NAME of it. Finding the max (w/o the corresponding name or ordinal position) is more-or-less a shakesperian exercize (full of sound and fury, signifying nothing).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GGleason]

You might try the following:
[tt]
For i = 0 To rstTableName.Fields.Count - 1
strFieldName = rstTableName.Fields(i).Name
varFieldData = rstTableName.Fields(i).Value
Next i
[/tt]
hth,
GGleason

 

[MichaelRed]

Hmmmmmmmmmmm,

This is NOT quite that simplistic. The following, however, is a SIMPLISTIC demo which WITHIN NARROW LIMITS sows "work".

The first block of "Code" is a sample/demo table. The first "line" (StuName through Asgn10) are the field names, while the corresponding "lines" are the sample records.

StuName	Asgn01	Asgn02	Asgn03	Asgn04	Asgn05	Asgn06	Asgn07	Asgn08	Asgn09	Asgn10
Brett Duvall	12	13	11	0	11	14	15	13	15	16
Gary Sibley	3	14	16	9	16	14	16	8	13	17
Michael Red	4	16	17	19	12	16	18	17	14	14
Tim Carroll	6	14	15	16	13	15	0	17	14	12
Penny Thompson	0	10	0	9	0	12	13	16	16	7
Jim Ehle	19	16	18	17	16	18	16	15	9	17
Rita Havenwood	17	16	19	14	15	16	0	12	17	18

The next block of "Code" is just the test "call"" to the routine, with the response.

? basMaxField("tblGrade", "StuName", "Brett Duvall", 12, 13, 11, 0, 14, 15, 13, 16)
Asgn10
[code]

This block of code is the actual routine:
[code]
Public Function basMaxField(MyTbl As String, KeyFld As String, _
                            KeyVal As Variant, _
                            ParamArray MyAry() As Variant) As String

    'Michael Red    5/7/2002    To Return the Fieldname of
    'the MAX value of a set of values from a group of fields

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(MyTbl, dbOpenDynaset)

    Dim Idx As Integer
    Dim Jdx As Integer
    Dim MaxVal As Variant
    Dim MaxField As String

    MaxVal = -1 * e ^ 38

    rst.FindFirst (KeyFld = KeyVal)

    Do While Idx <= UBound(MyAry)   ' To rstTableName.Fields.Count - 1
        If (MyAry(Idx) > MaxVal) Then
            MaxVal = MyAry(Idx)
            Do While Jdx <= rst.Fields.Count - 1
                If (rst.Fields(Jdx).Value = MaxVal) Then
                    MaxField = rst.Fields(Jdx).name
                    Exit Do
                End If
                Jdx = Jdx + 1
            Loop
        End If
        Idx = Idx + 1
    Loop

    basMaxField = MaxField

End Function

For this simplistic process, you need to know the name of the recordset (table), the name AND "key" value of a unique field, and the set of values to search for the max value. This routine returns the name of the field which contains the max value, leaving hte retrieval of the value itself as an exercize for the "student".

MUCH is left out of the routine, as it is NOT intended to be used in any actual process, but ONLY to be used to "show" AN approach to finding the field name from the associated value. This 'procedure' has NO error checking, and does NOT consider the possability of duplicate "Max" values in the set - much less how to handle these.

In pratice, a routine of this nature would probably be called from a procedure (e.g. "CODE&quot which would pass field names from the recordset as the parameters. I have deliberatly avoided this in the sample.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over