I'm trying query an access database from excel but I'm getting

Run-Time error '3075':

Syntax error (missing operator) in query expression
'(OpLogJobDataID) = & pk'.


in this line og code:


Set rs1 = db.OpenRecordset( _
"select (JobName) from tbl_OperatorLogJobData Where
(OpLogJobDataID) = & pk")

If I replace the line with:


Set rs1 = db.OpenRecordset( _
'"select (JobName) from tbl_OperatorLogJobData Where
(OpLogJobDataID) = 25")

I get no error and the code work.



My full code:



Sub agent()

Dim db As Database, rs1 As Recordset, r As Long, ur As Long
Dim pk As Long


Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

pk = Range("B100").Value





Set rs1 = db.OpenRecordset( _
"select (JobName) from tbl_OperatorLogJobData Where
(OpLogJobDataID) = & pk")


Range("D2").Value = rs1(0).Value



rs1.Close
Set rs1 = Nothing
db.Close
Set db = Nothing



End Sub

Hi Penny,

The Select statement is entered as a string. Because pk is a variable and
you are concatenating it with the remainder of the query string you appear to
have the double quotes closed off at the wrong position. (Anything within the
quotes is just part of the string. If you want to add a variable to the
string then close the double quotes and the ampersand and variable after it.)

Try the following

Set rs1 = db.OpenRecordset( _
"select (JobName) from tbl_OperatorLogJobData Where
(OpLogJobDataID) =" & pk )


--
Regards,

OssieMac

Thank you very much that worked





On Fri, 11 Dec 2009 20:36:03 -0800, OssieMac
wrote:

Set rs1 = db.OpenRecordset( _
"select (JobName) from tbl_OperatorLogJobData Where
(OpLogJobDataID) =" & pk )