return field name in query (1 Viewer)

[boycie]

Hi,

I am trying to return the name of a field if it meets my criteria eg.
Field A = 25
Field B = 35
Field C = 30

If I want to return the field with the max. number I would like Field D to say "Field B"

Hope I've explained well enough - Thanks

 

[crosmill]

Are you trying to return the result in a form text box or filter out the result in a query?

 

[boycie]

Thanks for the reply - I am trying to create another field in the same query which will name the max. field from the previous fields which all contain numbers eg.
sat sun mon tues Best Day
3 5 2 1 sun

ps. I am not using days, just the best scenario I can show. Thanks

 

[crosmill]

OK the best solution I can come up with is, in the new field in design view on the query, wirte a series of nested If(IIf) statements. Basically you need to test ALL the options to find out which is the greatest.

I don't know how you are with IF statements, but if you've never done them before start small (just test a few variables) so you can work out how they think.

IIf(Valiable1 is GreaterThan Variable2 then DoThis, Else DoThis)

Thats the structure

IIf(Mon > Tues, "Monday is the greatest", "Tuesday is the greatest")

Thats a basic statement

Instead of writing the text as a result, what you need to do put in more tests, ie instead of "Monday is..." you need to put IIF(Mon > Wed ... etc It'll be very long and complicated though.

IIf(Mon > Tues, (IIF(Mon > Wed ,"Monday is the greatest",), "Tuesday is the greatest")

And having writen all that I checked in the help files and found the
DMax(expr, domain[, criteria]) function.
I've never used so I don't know if it'll do what you want.

Good luck

 

[Pat Hartman]

There are no functions that will help you since the aggregate functions only work on multiple rows for a single column NOT multiple columns for a single row.

Create a public VBA function and call that. The simplest method is a series of If statements.

Public Function FindName(Fld1, Fld2, Fld3,...) As String
Dim HoldAmt
HoldAmt = Fld1
HoldName = "Fld1"
If Fld2 > HoldAmt
HoldAmt = Fld2
FindName = "Fld2"
End If
If Fld3 > HoldAmt
HoldAmt = Fld3
FindName = "Fld3"
End If
If Fld4 ...

End Function

Select FldA, FldB, FindName(Fld1,Fld2,Fld3,...) As FldName
From YourTable;

 

[raskew]

Function MaxValue() (below) returns the highest value from an array
of values. I've modified it slightly to meet your specification.

As opposed to the nested If solution, this function is not limited by
the number of values passed to it.

You can call it from a query as follows. Note that while the function
will always select the maximum value, displaying the correct field
name depends on the field being displayed sequentially.


SELECT [Field A], [Field B], [Field C], MaxValue([Field A],[Field B],[Field C])
AS Field_D FROM tblFieldTest;

Function MaxValue(ParamArray args()) As Variant
'*******************************************
'Name: MaxValue (Function)
'Purpose: Return Highest numeric value
'Inputs: ? maxvalue(3, 5, 4)
'Output: 5
'*******************************************

Dim intLoop As Integer
Dim varvalue As Variant
Dim intSave As Integer

varvalue = args(0)
intSave = 0
For intLoop = 1 To UBound(args())
If args(intLoop) > varvalue Then
varvalue = CDbl(args(intLoop))
'added
intSave = intLoop
End If
Next

'MaxValue = varvalue
MaxValue = "Field " & Chr(64 + intSave + 1) & ": " & varvalue

End Function

 

[boycie]

Thanks to crosmill, Pat and raskew - I will book next week off work and work through all your solutions until it works!!! Thanks