How To Use a Variable In a SQL String VBA

By Jaime Avelar

i Ablestock.com/AbleStock.com/Getty Images

Using variables in SQL statements can be tricky, but they can give you the flexibility needed to reuse a single SQL statement to query different data. In Visual Basic for Applications (VBA) you can build SQL statements that can contain string criteria. To use a string variable in a SQL string statement you must use the (") as the string delimiter and apply single quotation marks (') around the variable. Use variables in your SQL string instead of rewriting a SQL statement over and over to query data using different criteria.

Start by creating two variables you will use in VBA code, one to hold your variable value and the other to hold the SQL string. Type the following to create your variables:

Dim mySQLVariable As String

Dim strSQL As String

Set a value to the variable you will use in your SQL string such as the following:

mySQLVariable = "Sales Manger"

Define your SQL string such as the following:

strSQL = "SELECT Employees.[First Name], "

strSQL = strSQL & "Employees.[Last Name], "

strSQL = strSQL & "Employees.[Job Title] "

strSQL = strSQL & "FROM Employees "

strSQL = strSQL & "WHERE (((Employees.[Job Title])='Sales Manager'));"

Edit the last line of code in the previous step and replace "Sales Manager" with your variable like the following:

strSQL = strSQL & "WHERE (((Employees.[Job Title])='" & (mySQLVariable) & "'));"

References

Writer Bio