SQL result to msgbox

[Ivan_a]

Hello everyone,

I am trying to display the result of a SQL select query in a message box in access 2016. My initial thinking was that I need to use Openrecordset, but for some reason I can't manage to get it to work.

This is my code:


sqlstring = "Select [Current Period] from tblCHinfo"

Set Msgstring = CurrentDb.OpenRecordset(sqlstring)

MsgBox (Msgstring)


I also tried to search in the forum for an older topic on this, but couldn't fine one. Is anyone able to help out with this one ?

 

[welshgasman]

I'd probably use DLOOKUP

strMsg = DLOOKUP("[Current Period]","tblCHInfo")
MsgBox (strMsg)

 

[Ivan_a]

I'd probably use DLOOKUP

strMsg = DLOOKUP("[Current Period]","tblCHInfo")
MsgBox (strMsg)

Worked perfectly, thank you so much !

 

[Micron]

Why it didn't work: SET is for assigning objects to object variables (as in a recordset to a recordset object). You can't put an object into a message box.
Also, your recordset has no criteria, so likely you could get more than one record returned by your sql or query. When you don't provide criteria for a query/sql or domain function (such as DLookup) you will get whatever record Access considers to be the first one, and it may not be the first one you see in the table or query. Usually you could rely on the 1st record of a query if you do sorting, but you're not doing that either.