OpenRecordset results in "syntax error (missing operator)" messag

[Guest]

I have two OpenRecordsets that work fine separately. When I try to AND them
together I get the following message: "... syntax error (missing operator)
in query expression ...". The two OpenRecordsets are below, followed by the
flawed OpenRecordset with the And:

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
[Manager #]=" & [Manager], dbOpenDynaset) ' works fine

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where [Agent
Name]= '" & AgtName & "'", dbOpenDynaset) ' works fine

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
([Manager #]= & [Manager] and [Agent Name]= '" & AgtName & "'",
dbOpenDynaset) ' results in syntax error

 

[SusanV]

Try using this syntax:
Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
([Manager #]= & [Manager] AND WHERE [Agent Name]= '" & AgtName & "'",
dbOpenDynaset)

 

[fredg]

I have two OpenRecordsets that work fine separately. When I try to AND them
together I get the following message: "... syntax error (missing operator)
in query expression ...". The two OpenRecordsets are below, followed by the
flawed OpenRecordset with the And:

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
[Manager #]=" & [Manager], dbOpenDynaset) ' works fine

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where [Agent
Name]= '" & AgtName & "'", dbOpenDynaset) ' works fine

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
([Manager #]= & [Manager] and [Agent Name]= '" & AgtName & "'",
dbOpenDynaset) ' results in syntax error

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
([Manager #]= " & [Manager] & " and [Agent Name]= '" & AgtName & "'",
dbOpenDynaset)

 

[John Spencer (MVP)]

IS it me? I see an extra ( in the SQL statement between "WHERE " and "[Manager
#]". At least I don't see the balancing ")"

I have two OpenRecordsets that work fine separately. When I try to AND them
together I get the following message: "... syntax error (missing operator)
in query expression ...". The two OpenRecordsets are below, followed by the
flawed OpenRecordset with the And:

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
[Manager #]=" & [Manager], dbOpenDynaset) ' works fine

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where [Agent
Name]= '" & AgtName & "'", dbOpenDynaset) ' works fine

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
([Manager #]= & [Manager] and [Agent Name]= '" & AgtName & "'",
dbOpenDynaset) ' results in syntax error

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
([Manager #]= " & [Manager] & " and [Agent Name]= '" & AgtName & "'",
dbOpenDynaset)