How To Use a Variable In a SQL String VBA


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) & "'));"

Related Searches


  • Photo Credit Images
Promoted By Zergnet


You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!