How to get values from select statement into a variable?

Question

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.

Answer 1

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 =)