CODEWITHSUNDEEP
ms-accessvbams-access-2010ms-access-2007
Stackbe
Stackbe

Reputation: 19

Save the result of a SQL query into a variable in VBA?

I am trying to assign the result of the query into the variable. I have one query (lets call it "Query1") that will check various criteria. I tried it as follows. 

   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim Variable1 As String

   strQry = "Query1"
   Set rst = CurrentDb.OpenRecordset(strQry)

 Variable1 = ????? 
   rst.Close
   Set rst = Nothing

I need help to complete the above code so that my variable will get the value returned by the query. Please note that my Query1 will either return a Null value or a single record.

Upvotes: 1

Views: 4137

Answers (2)

HansUp
HansUp

Reputation: 97131

I don't see why you need a recordset for this. You could use DLookup to fetch the value returned by Query1.

Dim Variable1 As Variant
Variable1 = DLookup("[column name here]", "Query1")

I used Variant for Variable1 so that it can accept Null. If you wish to keep it as String, you can use Nz() as Andre demonstrated to transform Null to a string.

Upvotes: 4

Andre
Andre

Reputation: 27644

If your query returns only a single column, and only zero or one records, you can use rst(0) to get the first field (the Fields collection is zero-based).

If rst.EOF Then
    ' covers "no records returned"
    Variable1 = ""
Else
    ' Nz() covers "NULL returned"
    Variable1 = Nz(rst(0), "")
End If

But using the field name from the query is better readable.

Variable1 = Nz(rst!myField, "")

You may also want to look into the DLookup() function, which is designed to return a single value.

Upvotes: 2

Related Questions