Results 1 to 4 of 4

Thread: Query

  1. #1
    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. #2
    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. #3
    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. #4
    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
  •