place a value of SQL sentence inside of variable of vb code

[thread]

Hi all,
i'm trying to faster proccess,is there a way to place a value fro SQL
sentence inside of vb variable?
for ex.
dim value as integer
docmd.runSQL "SELECT .......,sum([data])...."=>value=sum([data])

 

[pietlinden]

Hi all,
i'm trying to faster proccess,is there a way to place a value fro SQL
sentence inside of vb variable?
for ex.
dim value as integer
docmd.runSQL "SELECT .......,sum([data])...."=>value=sum([data])

you mean you're trying to open a SQL statement that runs a summary
function and then write that summary function's result to a variable?
You're going about it all wrong. RunSQL executes action queries. It
does not return recordsets/results. You need OpenRecordset for that.
Then, yes, you can return the results of a summation query to that and
then read the value of a field in the recordset.

Public MyFunction (arglist) as Long
dim rs as dao.recordset
set rs = db.openrecordset("SELECT...Sum(MyField) As MyTotal FROM
MyTable WHERE... GROUP BY SummaryField...", dbOpenSnapshot)
MyFunction = rs.Fields("MyTotal")
rs.close
set rs=nothing
End Function