# Thread: Query

1. Registered User
Join Date
Jun 2002
Location
Houston, TX
Posts
116

## Unanswered: Query

Is there a way to copare 3 fields and return the value of the largest one?

It must be Friday because I'm stumped!

texasalynn

2. Registered User
Join Date
Jun 2002
Location
Houston, TX
Posts
116
Never mind - I was tired. I switch my IIF statements and then it worked correctly.

If you interested - I had the second IIF as the 1st condition. So when the second condition was larger the statement didn't give that answer.
That probably doesn't explain it right.
IIf([CM/STD]>1,[CM/STD],IIf([CM>0 & PM=0]+[CM=PM]>0,1,0))

3. Registered User
Join Date
Oct 2002
Posts
4
How could this same thing (see below) be done if you wanted to compare 7 fields and return the value of the largest one? (Sorry if this is a dumb question, but I'm not much of a programmer!)

Originally posted by texasalynn
Never mind - I was tired. I switch my IIF statements and then it worked correctly.

If you interested - I had the second IIF as the 1st condition. So when the second condition was larger the statement didn't give that answer.
That probably doesn't explain it right.
IIf([CM/STD]>1,[CM/STD],IIf([CM>0 & PM=0]+[CM=PM]>0,1,0))

4. Registered User
Join Date
Oct 2001
Location
Chicago
Posts
440
I'm assuming that sorting 7 field values through nested IIF statement would make Access puke.

Here's one way solve it:

MY TABLE
FindLargest_t is a table with 8 fields. ID - autonumber and 7 other numeric fields named Field1-Field7.

ID,Field1,...,Field7
1,2323,3545,345345,23,343,57,45,3245,
2,346,6745,324,23546,8567,3245,12431,346534
3,6756,23423,56456,56,76,3443,345,4545
etc...

MY QUERY
SELECT FindLargest_t.ID, FindLargest([ID]) AS Largest
FROM FindLargest_t
WHERE (((FindLargest_t.ID)=2));

This query is selecting the ID and the return value of the FindLargest() function, which I pass the value of the ID field.

MY FUNCTION
Function FindLargest(ID As Integer) As Long

Dim dbsTmp As DAO.Database
Dim rstTmp As DAO.Recordset
Dim intCounter As Integer
Dim strSQL As String

On Error GoTo PROC_ERR

strSQL = "SELECT FindLargest_t.Field1, FindLargest_t.Field2," & _
" FindLargest_t.Field3, FindLargest_t.Field4, " & _
"FindLargest_t.Field5, FindLargest_t.Field6, " & _
"FindLargest_t.Field7, * " & _
"FROM FindLargest_t " & _
"WHERE (((FindLargest_t.ID)=" & ID & "));"

Set dbsTmp = CurrentDb()

' Open the recordset and loop through its fields
Set rstTmp = dbsTmp.OpenRecordset(strSQL, dbOpenDynaset)

With rstTmp
Do Until .EOF

' set Tmp varaible
intTemp = 0

For intCounter = 0 To .Fields.Count - 1

If .Fields(intCounter).Value > intTemp Then
intTemp = .Fields(intCounter).Value
End If

Next intCounter
.MoveNext
Loop
.Close
End With

dbsTmp.Close
Set dbsTmp = Nothing

FindLargest = intTemp

PROC_EXIT:
Exit Function

PROC_ERR:
FindLargest = 0
Resume PROC_EXIT

End Function

This function's hardcoded SQL recordset gets the ID passed to it from the query that calls it. It then loops through the fields and finds the highest value and returns it to the query.

-Matt

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•