Determine which variable has lowest value

[kwfrazier]

Hello all!

I need to compare 9 variables from a record and find out which one has the lowest value. Based on that, I can then assign a team to the overall record. I may need to weigh questions in case of a tie in score. I can write If/Then/Else statements to do this, but it seems rather cumbersome to write and definitely difficult to maintain later. I was thinking along the lines of calling a function from a query (getteam) and passing the 9 question values to it for calculation.

Function GetTeam(Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9) As String
Dim Team

<comparison example>
Q1 < other Qs Then
Team = &quot;TeamFoo&quot;
or
Q2 < other Qs Then
Team = &quot;TeamBlah&quot;
etc.,etc,



GetTeam = Team
End Function

I'm just not sure what the best way to run the comparison would be. A simple description would be to get the lowest question score and assign a team.
e.g. Q2 has lowest score so TeamBlah is what is returned to the query as the result for the record.

I appreciate any help - I've totally gone brain-numb on this one. If the If/Then/Else should be used then that's fine too, I just was looking for a 'clean' way to do it.



Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer

 

[MIKElaw]

Look up the MIN (SQL) and DMIN (DAO) functions in the help system. They will both do what you want.



Mike

 

[Bullsandbears123]

Fuction getteam(q1 as double,q2 as double, q3 as double, q4 as double) as string

dim team as string
dim low

low=q1
team=&quot;myteam1&quot;

If low>q2 then
low=q2
team=&quot;myteam2&quot;
end if

If low>q3 then
low=q3
team=&quot;myteam3&quot;
end if

If low>q4 then
low=q4
team=&quot;myteam4&quot;
end if

etc.

GetTeam = Team
End Function

Although my preference would be to input the value into an array then loop through it like this

dim myarray(8,1)
myarray(0,0)=&quot;team1&quot;
myarray(0,1)=15
myarray(1,0)=&quot;team2&quot;
myarray(1,1)=12
myarray(2,0)=&quot;team3&quot;
myarray(2,1)=2
etc.

then pass entire array in to fuction getteam(myarray)

Function Getteam(my array)as string
dim team
dim low
dim i
low=myarray(0,1)

For i = 0 To UBound(myarray)
If low> myarray(i,1) then
low=myarray(i,1)
team=myarray(i,0)
next i
end if
getteam=team
end function

 

[MichaelRed]

These have been posted severally within htese fora, and can sometimes be found w/ 'advanced' search. Alas, alacl and awry, not today, so here again. Similar 'rowset' functions can generally be contrived for many (most ? all?) of the SQL aggregates, and I have several others which I have already found a use for.

Public Function basMaxVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MAXIMUM or a series of values

    Dim Idx As Integer
    Dim MyMax As Variant

    For Idx = 0 To UBound(varMyVals())
        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If
        If (varMyVals(Idx) > MyMax) Then
            MyMax = varMyVals(Idx)
        End If
NextVal:
    Next Idx

    basMaxVal = MyMax

End Function
Public Function basMinVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MINIMUM or a series of values

    'Sample Usage:
    '? basMinVal(1, 5, 9, 3, 13.663)
    '1

    '?basMinVal(9, 1, 5, 3, 13.663)
    '1

    Dim Idx As Integer
    Dim MyMin As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
     Else
        MyMin = varMyVals(0)
    End If

    For Idx = 0 To UBound(varMyVals())
        If (varMyVals(Idx) < MyMin) Then
            MyMin = varMyVals(Idx)
        End If
    Next Idx

    basMinVal = MyMin

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places