Select statement to run Access Query from VBA

"MikeV06" wrote in message
...
I need to run an Access query (2003) from VBA and put the result (one row,
one column) into a local variable. I have tried a public defining
pstcorpname as a string, acresult, acdataobject, and so on. I have tried
strcorpname. It puts it into an Access table (pstrcorpname or
strcorpname) with delete and store 1 record confirmation messages. I
have
been working on this all day without getting any closer.

Any ideas?


Private Sub getcorpname()
Dim SQL As String
SQL = "SELECT [tblCorp Name].CorpName INTO pstrcorpname " & _
"FROM [tblCorp Name] " & _
"WHERE ((([tblCorp Name].Corp)=" & pstrco & "))"
MsgBox ("SQL = " & SQL)
DoCmd.RunSQL SQL
MsgBox ("pstrco = " & pstrco & " pstrcorpname = " & pstrcorpname)
End Sub


You can't "SELECT INTO" a local variable. You can open a recordset on a
SELECT query, and get the value from the recordset:

Dim SQL As String
Dim pstrcorpname As String

SQL = "SELECT CorpName FROM [tblCorp Name] " & _
"WHERE Corp=" & pstrco

With CurrentDb.OpenRecordset(SQL)
If Not .EOF Then
pstrcorpname = !CorpName
End If
.Close
End With

MsgBox "pstrco = " & pstrco & " pstrcorpname = " & pstrcorpname

Or you can use the domain aggregate function DLookup to do this query
"behind the scenes" for you:

pstrcorpname = _
Nz(DLookup("CorpName", "[tblCorp Name]", "Corp=" & strco), "")

MsgBox "pstrco = " & pstrco & " pstrcorpname = " & pstrcorpname


Note that I wrapped the DLookup call in a call to Nz(), to convert the
possible Null result into a zero-length string. That's just in case there
might not be a record on file with Corp = strco.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)