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
<OssieMac@discussions.microsoft.com> wrote:

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