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

Question

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.

what are the column names in query1?, also you appear to have a mismatch with strSQL and strQry — LiamH
– LiamH, Commented Dec 2, 2015 at 13:48

HansUp · Accepted Answer · 2015-12-02 14:21:43Z

4

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.

answered Dec 2, 2015 at 14:21

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andre · Accepted Answer · 2015-12-02 14:14:33Z

2

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.

answered Dec 2, 2015 at 14:14

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related