Using a VBA variable in SELECT Sql statement

Question

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.

rcfmonarch · Accepted Answer · 2014-09-04 12:41:59Z

3

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.

answered Sep 4, 2014 at 12:41

rcfmonarch

1532 gold badges2 silver badges13 bronze badges

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

1 Comment

Paul Clint Over a year ago

yes I have done it as above but a dialog box appears asking me to enter the parameter value for mytable.English ;please help

Paul Clint · Accepted Answer · 2014-09-04 13:27:06Z

2

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

answered Sep 4, 2014 at 13:27

Paul Clint

1011 gold badge2 silver badges10 bronze badges

Collectives™ on Stack Overflow

Using a VBA variable in SELECT Sql statement

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related