Result of a SQL query to a variable in VBA code?

[Guest_imported]

How could I place the result of a SQL query/statement to a variable by using VBA?

The sql statement would be something like this:

SELECT Max([Field2]) AS MaxValue
FROM Table1
WHERE Field1 = 'match';

And the variable.. :

Dim varResult As Variant

I would be very grateful if somebody could help me with this.

 

[GHolden]

You can use something like...

Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim intResult As Integer

strSQL = "SELECT Max(fldOne) AS MaxValue FROM tblOne;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
intResult = rst!MaxValue
rst.Close

This assumes that the SQL only returns one value, which obviously it true if you're using Max. There are two ways to write error-free programs; only the third one works.

 

[Guest_imported]

Thank you, GHolden!! You saved my day (and maybe the whole week)!

I needed to change

Dim rst As Recordset

to

Dim rst As DAO.Recordset

After that it started to work just like I hoped it would work.

 

[apkohn]

Alternatively you could use the Domain Lookup functions, such as Dmax, Dlookup etc...

intResult = Dmax("Field2","Table1","[Field1]=" & match)