Max query problem

[roelandkim]

I have a table as follows

Othersource____PAsource____Wagesource
5_____________8__________0
8_____________0__________2
5_____________5__________9

I am trying to do 2 things, first get the maximum value for the 3 sources, which I have been able to do using a zmax() function posted on another thread. I also want to see the source name, such that my end result looks like this:

Othersource_PAsource_Wagesource_MaxVal_MaxSource
5__________8__________0__________8_____PASource
8__________0__________2__________8_____Othersource
5__________5__________9__________9_____Wagesource

The Zmax() funtion I am using is as follows:
Public Function zMax(ParamArray values() As Variant) As Variant
Const loend As Double = -1000
Dim val As Variant
zMax = loend
For Each val In values
If val > zMax Then
zMax = val
End If
Next val
End Function

Any help would be much appreciated.

Thanks,
Roeland Kim

 

[Jon K]

Try these two expressions for the MaxVal and the MaxSource(type/paste each in a Field: cell in the query grid):-

MaxVal: IIf(IIf([Othersource]>[PAsource],[Othersource],[PAsource])>[Wagesource],IIf([Othersource]>[PAsource],[Othersource],[PAsource]),[Wagesource])

MaxSource: IIf(IIf([Othersource]>[PAsource],[Othersource],[PAsource])>[Wagesource],IIf([Othersource]>[PAsource],"Othersource","PAsource"),"Wagesource")

 

[roelandkim]

Great, works like a charm. Thanks for the help.