How to pass a SQL query result to variable with VBA Access

Question

I need to create a standalone function which checks whether a value is true or false. I have attempted to set up a SQL query that does this, but can't get the result to pass to the variable.

The query will always return only one record.

How can I pass the result of the SQL string as the returning value of the function?

UPDATED

Private Function HasBM(iMandate As Variant) As Boolean
'Returns boolean value for whether or not mandate has a benchmark
Dim sSQL1 As String
Dim sSQL2 As String

Dim db As Database
Dim rs As Recordset

sSQL1 = "SELECT tbl_Mandate_Type.BM_Included " & _
        "FROM tbl_Mandate_Type INNER JOIN tbl_MoPo_BM ON tbl_Mandate_Type.Mandate_Type_ID = tbl_MoPo_BM.MandateType_ID " & _
        "WHERE (((tbl_MoPo_BM.MoPo_BM_ID)="

sSQL2 = "));"

Set db = CurrentDb
Set rs = db.Execute(sSQL1 & iMandate & sSQL2)
HasBM = rs.Fields(0).Value

End Function

UPDATE 2: SOLUTION

Thanks for Magisch and HansUp I ended up getting two solutions that work:

DLOOKUP Solution by Magisch as I implemented it:

Private Function HasBM2(iMandate As Variant)
'Returns boolean value for whether or not mandate has a benchmark
Dim tmp As String

tmp = CStr(DLookup("tbl_MoPo_BM.MandateType_ID", "tbl_MoPo_BM", "tbl_MoPo_BM.MoPo_BM_ID = " & iMandate))
HasBM2 = DLookup("tbl_Mandate_Type.BM_Included", "tbl_Mandate_Type", "Mandate_Type_ID =" & tmp)

End Function

Second Solution using recordset as HansUp helped create:

Private Function HasBM(iMandate As Variant) As Boolean
'Returns boolean value for whether or not mandate has a benchmark
Dim sSQL1 As String
Dim sSQL2 As String

Dim db As Database
Dim rs As dao.Recordset

sSQL1 = "SELECT tbl_Mandate_Type.BM_Included " & _
        "FROM tbl_Mandate_Type INNER JOIN tbl_MoPo_BM ON tbl_Mandate_Type.Mandate_Type_ID = tbl_MoPo_BM.MandateType_ID " & _
        "WHERE (((tbl_MoPo_BM.MoPo_BM_ID)="

sSQL2 = "));"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL1 & iMandate & sSQL2)
HasBM = rs.Fields(0).Value

rs.close    

End Function

DoCmd.RunSQL is for action SQL only (insert, delete, update). To return results you'll need a recordset or something similar. — Magisch
I was just exploring a similar solution... but still can't get it to work quite right. — rohrl77
Declare the rs variable like this: Dim rs As DAO.Recordset Does that eliminate the mismatch error? — HansUp
For the second solution, please do rs.close too to avoid memory leaks or other shenanigans. — Magisch

Magisch Magisch · Accepted Answer · 2015-12-09T14:30:59

You can use the DLookup function native to access for this. Since you have an INNER JOIN in your SQL, you will have to use it twice, but it'll still work.

Use a temporary string variable to store the temporary output, like this:

Dim tmp as String
tmp = Cstr(DLookup("tbl_MoPo_BM.MandateType_ID","tbl_MoPo_BM","tbl_MoPo_BM.MoPo_BM_ID = " & iMandate)) 'This is to fetch the ID you performed the inner join on
HasBM = DLookup("tbl_Mandate_Type.BM_Included","tbl_Mandate_Type","Mandate_Type_ID =" & tmp) ' this is to get your value

Using your DoCmd.RunSQL will not suffice since its for action queries only (INSERT, DELETE, UPDATE) and incapable of returning the result.

Alternatively, you can use a Recordset with your SQL query to fetch what you want, but thats more work and the DLookup is designed to exactly avoid doing that for single column return selects.

How to pass a SQL query result to variable with VBA Access

1 Answers