CODEWITHSUNDEEP
sqlms-accessvbarecordset
G.Chahar
G.Chahar

Reputation: 185

How to get values from select statement into a variable?

I want to store the result from select statement into a variable that I can use to show in a message.

Dim rst As DAO.Recordset
Dim result As String

Dim strSQL As String
strSQL = "SELECT Key_Old FROM Pivot_Old where Pivot_Old.Count = " & 1 & ""
   
Set rst = CurrentDb.OpenRecordset(strSQL)

result = rst!result
rst.Close

MsgBox ("String is " & result)

I want the result from the select statement to display in a msgbox.

Upvotes: 3

Views: 9092

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

result = rst!result

This notation is magical shorthand for this:

result = rst.Fields("result").Value

And your query doesn't have any result field. So either you change the query to alias Key_Old to result:

strSQL = "SELECT Key_Old As result FROM Pivot_Old where Pivot_Old.Count = " & 1 & ""
...
result = rst.Fields("result").Value

Or, you change how you're referring to that field:

strSQL = "SELECT Key_Old FROM Pivot_Old where Pivot_Old.Count = " & 1 & ""
...
result = rst.Fields("Key_Old").Value

Rule of thumb, prefer explicit code over magical shorthand notation =)

Upvotes: 4

Gustav
Gustav

Reputation: 55981

You could also do:

strSQL = "SELECT Key_Old FROM Pivot_Old where Pivot_Old.Count = " & 1 & ""

Set rst = CurrentDb.OpenRecordset(strSQL)

result = rst(0).Value
rst.Close

MsgBox ("String is " & result)

or keep it really simple:

result = Nz(DLookup("Key_Old", "Pivot_Old", "[Count] = " & 1 & ""))
MsgBox "String is " & result

Upvotes: 0

Related Questions