Too few parameters error in db.OpenRecordset

[Reg]

I am getting this error:
Runtime Error 3061
Too few parameters. Expected 1

at the line:
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

When I run this code :

thisSector = lstSectors.Column(1)
Dim db As Database, rstSector As Recordset, strSQL As String

strSQL = "SELECT Reps.Email
FROM (Reps INNER JOIN Companies
ON Reps.CompanyID = Companies.CompanyID)
INNER JOIN Sector ON Companies.SectorID = Sector.SectorID
WHERE (Sector.SectorName)=thisSector"

Set db = CurrentDb()
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

I would be grateful if someone could tell me where I am going wrong.
Thanks
Reg

 

[Douglas J. Steele]

Try changing your code to:

Dim db As Database, rstSector As DAO.Recordset, strSQL As String

I'm guessing that you have a reference to both ADO and DAO, and that the ADO
reference is higher in the list. When that's the case, simply using As
Recordset will result in an ADO recordset, whereas you need a DAO recordset.

 

[Amy Blankenship]

Try Sector.SectorName='thisSector'

HTH;

Amy

 

[Guest]

Reg:

Concatenate the value of the thisSector variable into the string expression.
If it’s a number:

WHERE (Sector.SectorName)=" & thisSector

Or if thisSector is text:

WHERE (Sector.SectorName)=""" & thisSector & """"

Ken Sheridan
Stafford, England

 

[Reg]

Thanks for the suggestions everyone.
This last solution fixed it for me.
Thanks a lot Ken!

Reg