syntax error in the select statement

[kavya]

I am getting syntax error in the select statement.
sqlstr = " select cik, membersshipID from BDExchange where cik = " & rs2.Fields("CIK&quotand membersshipID = " & rs2.fields("MembersshipID&quot

I am trying to do this "select cik, membersshipID from BDexchange where cik = (something) and membersshipID = (something)


Public Static Sub DEAMem()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim sqlstr As String
Dim strexam As String
strexam = "PND"
Set db = CurrentDb
Set rs2 = db.OpenRecordset("qryBDExchange", 8)
While Not rs2.EOF
sqlstr = " select cik, membersshipID from BDExchange where cik = " & rs2.Fields("CIK&quotand membersshipID = " & rs2.fields("MembersshipID&quot
Set rs1 = db.OpenRecordset(sqlstr, dbOpenDynaset)
If rs1.EOF Then
With rs1
.AddNew
.Fields("CIK&quot = rs2.Fields("cik&quot
.Fields("MembersshipID&quot = rs2.Fields("MembersshipID&quot
.Update
End With
End If
rs1.Close
Set rs1 = Nothing
rs2.MoveNext
Wend
rs2.Close
Set rs2 = Nothing

End Sub


Thanks for all your help

 

[Guest_imported]

Have you tried?

sqlstr = "select cik, membersshipID from BDExchange where cik = '" & rs2.Fields("CIK&quot & "' and membersshipID = '" & rs2.fields("MembersshipID&quot & "'"

Note the quotes added.

Hope it helps

AmaHerrn

 

[rewdee]

I believe it is somthing to do with the spacing and quotes if it is a string. If cik and membershipId are number fields the sql string should be:

sqlstr=" select cik, membersshipID from BDExchange where cik = " & rs2.Fields("CIK&quot & " and membersshipID = " & rs2.fields("MembersshipID&quot

If CIK is text the string should look like this:

sqlstr=" select cik, membersshipID from BDExchange where cik = " & chr(34) & rs2.Fields("CIK&quot& chr(34) & " and membersshipID = " & rs2.fields("MembersshipID&quot

A good tip to see what's wrong with your sqlstr is to add a line of code after setting your sqlstr is:

debug.print sqlstr

run the code. Goto to your debug window (ctrl G), copy and paste string into QBE (query module-click SQL) and try running.

Hope this helps,
Rewdee

 

[kavya]

Thanks Guys.
I appreciate.