Passing a string variable in SQL query

Question

I am trying to write a SQL query that takes in WHERE conditional value from a variable.

The variable is a string type and it is being extracted first from a different table using a different query through the use of recordsets. I think I got the quotations right in the query but it shows "Undefined function 'rsDB' in expression" error.

extPMBActID_SQL = "SELECT * FROM PMB;"
Set rsDB = CurrentDb.OpenRecordset(extPMBActID_SQL, dbOpenDynaset)
testID = Left(rsDB.Fields("ActivityID"), 2)
instRptGen_SQL = "SELECT AreaName FROM Area WHERE AreaID =" & "Left(rsDB.Fields('ActivityID'), 2)" & ";"
Set rsDB = CurrentDb.OpenRecordset(instRptGen_SQL, dbOpenDynaset)

I think whenever the query is ran, it is somehow not able to fetch rsDB.Fields("ActivityID") value. I am sure that testID variable does have the value.

Any ideas what I am missing here?

Tim Williams · Accepted Answer · 2019-11-05 00:54:17Z

1

Remove the quotes from that second part:

instRptGen_SQL = "SELECT AreaName FROM Area WHERE AreaID =" & _
            Left(rsDB.Fields("ActivityID").Value, 2)

You don't need the terminating ;, and if AreaID is not numeric you need single quotes around the value:

instRptGen_SQL = "SELECT AreaName FROM Area WHERE AreaID ='" & _
            Left(rsDB.Fields("ActivityID").Value, 2) & "'"

edited Nov 5, 2019 at 0:54

answered Nov 5, 2019 at 0:48

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AutoMate Over a year ago

I did try this a whle ago and it throws me data type mismatch error... The Left(rsDB... is a string so I am guessing it needs to in quotes. I might be wrong...

AutoMate Over a year ago

Do you think I can pass a SELECT query into Values parameter of INSERT INTO statement? I built a query that looks something like this:

instRptGen_SQL = "INSERT INTO testTable (ID, arbName) VALUES(Left(rsDB('ActivityID'), 2), 'SELECT AreaName FROM Area WHERE AreaID =' & Left(rs.Fields('ActivityID'), 2))"

It is giving me invalid operation error..

Tim Williams Over a year ago

stackoverflow.com/questions/17757348/…

Collectives™ on Stack Overflow

Passing a string variable in SQL query

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related