CODEWITHSUNDEEP
sqlms-accessvba
Alex Gordon
Alex Gordon

Reputation: 60882

How to run a query and put the result into a variable

I am running a query from the VBA editor of Access:

select max(somerow) from sometable

I want to put the result of this query into a VBA variable. How do i do it?

Upvotes: 0

Views: 20485

Answers (2)

marg
marg

Reputation: 2827

If you just want the Max value, you should consider using HansUps Solution.

Here is a solution using DAO:

Dim rs As DAO.Recordset
Dim sqlMax As String
Dim result As Integer

sqlMax = "select max(somerow) from sometable"
Set rs = CurrentDb.OpenRecordset(sqlMax)

If rs.Fields.Count = 1 Then
    result = rs.Fields(0)
End If

Set rs = Nothing

You will need to add a Reference to the Microsoft DAO Object library through Tools->References in the VBA Editor

Upvotes: 2

HansUp
HansUp

Reputation: 97131

Look at Access Help for DMax function.

Dim varSomething As Variant
varSomething = DMax("somerow", "sometable")

Edit: I realize that suggestion is not what you were looking for. But it seems to me you may be taking the long way round to achieve something that is simple with the DMax domain function.

Upvotes: 3

Related Questions