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) & "'));"
- Photo Credit Ablestock.com/AbleStock.com/Getty Images
Access 2007: The VBA Set String Variable to Null
Visual Basic for Applications (VBA) is a programming language used for Microsoft Access. You set a string variable to null to clear...
How to Create a Global Variable in SSIS
SSIS is a component of Microsoft SQL Server that was implemented with the version released in 2005. SSIS provides the ability to...
How to Convert Date to String in SQL
Structured Query Language (SQL) provides you with the tools to update, delete, select and edit your table's data. SQL is similar to...
How to Use SQL Variables in Oracle
SQL stands for Structured Query Language and is used by database developers to retrieve, update and insert data into databases. PL/SQL, or...
SQL VBA Tutorial
Structured Query Language (SQL) is the computer language used for managing relational databases. Visual Basic for Applications (VBA) is the programming language...
How to Use Variables to Select a Cell in VBA
Visual Basic for Applications (VBA) is a programming language used exclusively for Microsoft Office applications. VBA allows you to customize Excel by...
How to Use An "If Statement" in a "Where Clause" in TSQL
Microsoft Transact-SQL query language, or T-SQL, allows MS SQL database server users to create and set SQL query variables. An SQL variable...
How to Clean a String in VBA
As a Visual Basic for Applications programmer, you should be able to remove numbers or characters in strings to display correct information...