How can I store answer to an SQL statement in a variable

[Ben2k]

Is this possible in anyway

Dim answer as currency
answer = "SELECT SUM( field1) FROM table"

I want to run the SQL statement in the form_load sub and put the answer in a variable.

 

[Norie]

This won't work.

If it is really what you want try DSUM("field1", "table")

The only way to use SQL in code is using Recordsets.

 

[Ben2k]

Okay thanks,

ADO and stuff, think that maybe a little advanced for now, still trying to learn the basics!

 

[mdmilner]

Here is the technique.
It gets more complex, usually, but this basically works.

All it does is create a recordset - your SQL statement results in only a single record containing a single field then assign the value in the only record to the variable.

To use, you need to open up a module (existing or new) and create a function or subrouting. Try "Sub any_name_you_choose() -- it'll put an End Sub at the end. Then paste this code into the Sub.

Before you execute it, from within the Code Module go to the Tools Menu, Select References. Make sure that "Visual Basic for Applications", "Microsoft Access 9.0 Object Library" (could be a different version -- 9.0 is Office 2000) and "Microsoft DAO 3.6 Object Library" (again 3.6 is Office 2000) are selected.

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String
Dim answer As Currency

Set dbs = CurrentDb()

strSQL = "SELECT SUM( field1) FROM table"

Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

answer = rs.fields(0).Value

Set rs = Nothing
Set dbs = Nothing