Get value from a query and assign it to a variable

[scambigol]

I guess and hope the answer is simple, but I haven't managed to find it myself...

simply, I need to assign to variable LastDate the value of the query "Select max(Date) from Rankings;"

how do I do that?!
thanks in advance
scambigol

 

[SydneyGeek]

Hi scambigol,

This worked for me...
1. Built a query to return the latest order date.
2. Created a recordset based on the query and grabbed the value from the recordset.
Note: If you're using Access 2000 or later, you'll need to go to Tools | References in your open module and set a reference to the latest version of Microsoft Data Access Objects that you have on your system.
Code:

Sub WhatLastOrder()
    Dim LatestDate As Date
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs("zzLatestOrder")
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    LatestDate = rst![LastOrder]
    
    MsgBox LatestDate
    
    rst.Close
    
End Sub

In my case, the SQL for the query was

SELECT Max(tblOrders.OrderDate) AS LastOrder
FROM tblOrders;

Change your query and field names to suit.

Denis

 

[scambigol]

thanks for showing me the way - unfortunately one of the lines in the code doesn't seem to work, and I haven't manage to figure out why:

Dim latestDate As Date
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryMaxRankingsDate")
Set rst = qdf.OpenRecordset(dbOpenDynaset) <--------- this one

latestDate = rst![Expr1000]
MsgBox (latestDate)
rst.Close


when the code runs, I get the following error message:


RUN-TIME ERROR '3061'
TOO FEW PARAMETERS. EXPECTED 1.


but there precisely 1 parameter... i've tried putting in the other optional parameters but I haven't managed to find a way round it. any ideas?
thanks again
scambigol

 

[SydneyGeek]

Sorry, you weren't clear about the parameters and the Access chokes unless you expressly evaluate them. Like this:

 Sub WhatLastOrder()
    Dim LatestDate As Date
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter
   
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs("zzLatestOrder")
    For Each prm in qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    LatestDate = rst![LastOrder]
   
    MsgBox LatestDate
   
    rst.Close
   
End Sub

Denis

 

[Norie]

Why not just use DMax?

 

[scambigol]

thanks for the solutions, they both work
scambigol