open record set with sql

[Marc]

stPlanID = Me!agtplan
stTest = "[PlanID] = " & stPlanID & ""

this works:

rst.Open "SELECT * FROM tblAgtSplits WHERE ([PlanID] = 8)",_
CurrentProject.Connection

but this doesn't and I can't figure out why:

rst.Open "SELECT * FROM tblAgtSplits WHERE (stTest)",_
CurrentProject.Connection

 

[Brendan Reynolds]

rst.Open "SELECT * FROM tblAgtSplits WHERE (" & stTest & ")"

SQL Server doesn't 'know' that stTest is the name of a variable. You have to
get VBA to evaluate the variable and concatenate the value of the variable,
rather than its name, into the SQL statement.