Getting the result from DoCmd.RunSQL

[uhu]

Hi,

I'm trying to get the result from a DoCmd.RunSQL(sqlstr)to be recorded inside a variable. How do I do that ?

The SQL Query: "Select SUM (TotalCost) as Total from Sales".
txtTotal = ???? <- how to insert the result of the SQL query to this variable

Thanks in advance,
Vito

 

[BillPower]

This would be one way of doing it:

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strTotal As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;Select SUM (TotalCost) as Total from Sales;&quot
With rst
Do While Not .EOF
strTotal = .Fields(&quot;Total&quot.Value
MsgBox strTotal
.MoveNext
Loop
End With

You could also use DSum or possibly other Methods, I like this Method. Replace Msgbox with your variable name =:

Bill

 

[BillPower]

Actually uhu,

This would probably be better for your situation:

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strTotal As String
On Error GoTo Err_Sum
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;Select SUM (TotalCost) as Total from Sales;&quot
With rst
.MoveFirst
strTotal = .Fields(&quot;Total&quot.Value
MsgBox strTotal
End With
Exit Sub
Err_Sum:
MsgBox Err.Number & &quot; - &quot; & Err.Description

Regards

Your SQl should only return 1 Record, so there was no need for the .EOF and .Movenext etc in my previous suggestion. I've also added an Error Proc just in case no Record is returned.

Bill

 

[uhu]

Thanks so much Bill