CODEWITHSUNDEEP
sqlvba
Paul Clint
Paul Clint

Reputation: 101

Using a VBA variable in SELECT Sql statement

Is their a way to use a declared VBA variable in a SELECT Sql statement as follows (I have a table called myTable and i want to set the referenced field by passing the declared string myVar) : e.g passVar("English")

NB: Am using Microsoft Access

Private Function passVar(myVar as string)

Dim db As Database
Dim qdf As DAO.QueryDef
Dim stmnt As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("SelectSubjectQuery")

stmnt = "SELECT [myTable].[myVar] = " &Chr$(34) & myVar & Chr$(34) & _

         //other SQL statements go here

qdf.SQL = stmnt

End Function

Each time i run the query a dialog box appears asking for the value of mytable.myvar instead of passing myvar i.e Eglish as the referenced field on myTable.

Upvotes: 1

Views: 16551

Answers (2)

Paul Clint
Paul Clint

Reputation: 101

stmnt = "SELECT [myTable]." & myVar & _

Got it to working by writing as above (no need for the equals sign as the string is passed through the function). Thanks for the insight @rcfmonarch

Upvotes: 2

rcfmonarch
rcfmonarch

Reputation: 153

Yes, build the SQL statement as a string like this:

stmnt = "SELECT " & [myTable] & "." & [myVar] & " = " &Chr$(34) & myVar & Chr$(34) & _

VBA should then fill in the names for MyTable and MyVar when the code is run and the string is built.

Upvotes: 3

Related Questions