How to write query in access using NOT IN

[rmayur]

selectqry = "SELECT RP.* FROM Partners AS P, Redemption_report AS RP " & _
"WHERE P.partner_name = RP.redemption_partner And type ='Standard' and " & _
"RP.order_description not like '*_Stay*' and P.partner_name = 'Demo Units' and " & _
"RP.country_name NOT IN ("Israel", "Turkey", "Serbia", "South Africa")"



Here in place of Israel compile error coming "End of statement" ..
Plz any one can help me how to make it correct

[June7]

This expression really should be in a JOIN clause: P.partner_name = RP.redemption_partner

However, names are very poor key fields.

Use apostrophes as delimiters for the text parameters or double the quote marks.

"RP.country_name NOT IN ('Israel', 'Turkey', 'Serbia, 'South Africa')"

"RP.country_name NOT IN (""Israel"", ""Turkey"", ""Serbia"", ""South Africa"")"

[rmayur]

Thanks...

I write these way and it's get executed

selectqry = "SELECT RP.* FROM Partners AS P, Redemption_report AS RP " & _
"WHERE P.partner_name = RP.redemption_partner And type ='Standard' and " & _
"RP.order_description not like '*_Stay*' and P.partner_name = 'Demo Units' and " & _
"(RP.country_name NOT IN(" & Chr(34) & "Israel" & Chr(34) & "," & Chr(34) & "Turkey" & Chr(34) & "," & Chr(34) & "Serbia" & Chr(34) & "," & Chr(34) & "South Africa" & Chr(34) & "))"