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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Try using this syntax:
Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
([Manager #]= & [Manager] AND WHERE [Agent Name]= '" & AgtName & "'",
dbOpenDynaset)
 
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
Click to expand...

Set rs = CurrentDb.OpenRecordset("Select * From [AgentDataBase] Where
([Manager #]= " & [Manager] & " and [Agent Name]= '" & AgtName & "'",
dbOpenDynaset)
 
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
Click to expand...

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