put the result of query into variable

[rwahdan1978]

I have created a query (query design and I called it max) to get the max of numbers from multi fields in the same table. Example:



Exam1: 88, Exam2: 77, Exam3: 66, Exam4: 99 and if I run the query i get 99.

in form load I have:

docmd.openquery "max"

How to assign that into a variable to work on it?

[Micron]

Look up how to create a recordset from a query, and then get the value from any row or field in that recordset. If your query is only going to have 1 row in the results, your recordset will only contain 1 record.

Looks to me like your data isn't properly normalized if several values are in one row in the same field, or are spread across fields in a row. Also, you should not use reserved words for object names (max).
Here's a list
http://www.allenbrowne.com/AppIssueBadWord.html

EDIT - you could also use DLookup() against your query.

[kd2017]

Agreed with micron. It looks like your table isn't properly normalized. This would be easier to do and flexable if it were normalized.

Anyway here is a demonstration of how you can use queries in your code:

Public Function max_test(row_id) As Variant
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim results As Variant
    Dim qry As String
    
    qry = "SELECT Exam1, Exam2, Exam3, Exam4 FROM my_table WHERE my_id=" & row_id
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(qry, dbOpenSnapshot)
    If Not (rs.BOF And rs.EOF) Then
        results = rs!Exam1
        If rs!Exam2 > results Then results = rs!Exam2
        If rs!Exam3 > results Then results = rs!Exam3
        If rs!Exam4 > results Then results = rs!Exam4
    Else
        results = Null
    End If
    rs.Close
    
ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    max_test = results
    
    Exit Function
ErrHandler:
    MsgBox "Error #" & Err.Number & ": " & Err.Description, , "max_test() error"
    Resume ExitHandler
End Function


Public Sub do_work()
    Dim my_max As Variant
    
    my_max = max_test(99)
    
    If Not IsNull(my_max) Then
        Debug.Print my_max
    End If
End Sub

[madpiet]

I have created a query (query design and I called it max) to get the max of numbers from multi fields in the same table. Example:

Exam1: 88, Exam2: 77, Exam3: 66, Exam4: 99 and if I run the query i get 99.

in form load I have:

docmd.openquery "max"

How to assign that into a variable to work on it?

I'm on the "Normalize" team. <g> I've worked with tables that weren't normalized and the problem with them is that they make what would be simple queries far more difficult than they need to be. Say instead of your format, you had

(StudentID, Test, Grade)... so
(S001, Exam1, 88),
(5001, Exam2, 77)
(5001, Exam3, 66)
(5001, Exam4, 99)

SELECT StudentID, AVERAGE(Grade) As AvgGrade
FROM Mytable
GROUP BY StudentID

and it works no matter how many records you add. I would fix this now, before you get tons of data in it. Post a sample of your data (remove anything identifying... just include StudentID... don't want to know anything confidential!), and I can do it. (If I can't by now, sheesh, i'm in trouble!)

But a general rule of thumb is that if you have to change your table design to add more data, your table design isn't right. Another thing that's handy about a proper table design here is getting highest and lowest grades on a test are equally simple:

SELECT Test, MIN(Grade) as LowestGrade, MAX(Grade) as HighestGrade, AVG(Grade) As AverageGrade
FROM MyTable
GROUP BY Test;

But if you don't normalize, this is just hideous. (been there, done that. Do not recommend it!)

[June7]

kd, OP indicated already has a query that determines max exam so don't really need to do that in VBA. Just use DLookup() to pull single value from query.

[kd2017]

kd, OP indicated already has a query that determines max exam so don't really need to do that in VBA. Just use DLookup() to pull single value from query.

Understood. Big picture I was just demonstrating how one can work with queries in vba and just did it in a way I imagined was relevant to them at the moment. Afterall, that was what the question was about... Give a fish or teach to fish and all that.