VBA Store Query result in Variable

[ctechnumber1]

I am trying to store the result of a query like the one below which is more than DLookUp will handle.

Can someone tell me how to store the results of this query or like queries in a Variable?

*I have been doing this by using a record set and just getting the first value the storing that.

SELECT Systems.System_Name
FROM Obj_Groups, Systems INNER JOIN Obj_Sections ON Systems.System_ID = Obj_Sections.System_ID
GROUP BY Systems.System_Name
HAVING (([Obj_Groups].[Obj_Group_ID]="1"));

 

[ctechnumber1]

For now this is what I'm doing, in case others need to do this:

I have created my own Private function to pass in a SQL string and receive a string value in return for the query.

This will allow you to set a variable based on query:
Dim varMyVar As String
varMyVar = MyLookUp("SQL QUERY GOES HERE")

**Might need to make sure you are only returning one value off query

You may have to change Data Types to accommodate your circumstance
'''''''''''''''''''''''''''''Start Code'''''''''''''''''''''''''''''''''''
Private Function MyLookUp(SQLstring As String) As String
Dim objDB As DAO.Database
Set objDB = CurrentDb
Dim Value As String
Dim rsLookUp As DAO.Recordset
Dim rsLookUpField As DAO.Field
Set rsLookUp = objDB.OpenRecordset(SQLstring)

Do While Not rsLookUp.EOF
For Each rsLookUpField In rsLookUp.Fields
Value = rsLookUpField.Value
Next
rsLookUp.MoveNext
Loop

MyLookUp = Value

End Function
'''''''''''''''''''''''''''''Stop Code'''''''''''''''''''''''''''''''''''