Building SQL Statements that Include Variables and Controls in Access 2007
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
Summary: Learn how to build SQL statements in VBA that contain string criteria, date criteria, and numeric criteria. (4 printed pages)
Mike Stowe, Microsoft Corporation
March 2009
Applies to: Microsoft Office Access 2007, Microsoft Office Access 2003
Contents
Overview
Using String Criteria
Using Date and Time Criteria
Using Numeric Criteria
Working with Long SQL Statements
Conclusion
Additional Resources
Overview
Microsoft Office Access 2007 makes it is easy to build a database application by using forms that enable users to enter and edit data, and by providing pre-defined reports that answer business questions with little or no programming. Eventually, you may want to make your applications interactive and able to respond to user input dynamically. Two powerful tools that you can use to build dynamic applications are Structured Query Language (SQL) statements and Visual Basic for Applications (VBA).
SQL is the language that Access uses to define and retrieve data. As you build a query in Query Design mode, you are actually building the SQL statement that Access 2007 will use to retrieve the data based on the criteria that you specify.
How does SQL relate to VBA? SQL statements are used by many Access 2007 VBA functions, methods, and properties to define criteria that binds data to forms and form controls, sort, and filter the data displayed to the user in a form or in a report.
Building SQL statements in VBA involves concatenating literal SQL text with the contents of VBA variables, form, or report controls. This article describes how to build an SQL statement by using VBA.
Using String Criteria
The first thing that you have to learn about how to build SQL statements in VBA is how to handle string criteria. When you build an SQL statement in VBA that contains string criteria, you are embedding one string inside another. Each string is identified by a set of string delimiters. Both VBA and SQL use a quotation mark (") as the string delimiter.
Embedding one set of quotation marks within another set of quotation marks can be tricky. For example, if you want to select the records from the Orders table for a particular city. If you switch the Access query designer to SQL View, you can use the following SQL statement to select the orders that will ship to Charlotte.
SELECT * FROM Orders WHERE ShipCity = "Charlotte"
Knowing that the SQL statement is a string, and that VBA uses quotation marks to delimit a string, you might think that the following code example would assign the SQL statement to a variable.
strSQL = "SELECT * FROM Orders WHERE ShipCity = "Charlotte""
However, the statement will not compile. VBA parses the string by using the first two quotation marks (") that it encounters. Therefore, it cannot interpret the rest of the statement.
One solution is to double the number of quotation marks that enclose the city name. When the VBA compiler encounters two quotation marks (""), it handles them as a single quotation mark ("). In this code example, "Charlotte", becomes ""Charlotte"".
strSQL = "SELECT * FROM Orders WHERE ShipCity = ""Charlotte"""
This technique resolves the problem of embedding a literal criteria into a VBA SQL statement. However, it does not address how to use a variable or the value of a control. The following code example shows how use the value of the txtShipCity text box on the current form.
strSQL = "SELECT * FROM Orders WHERE ShipCity = """ & Me!txtShipCity & """"
As shown in the code example, referencing a single variable or control makes an otherwise short SQL statement into an exercise in counting the quotation marks. You might easily imagine how using a more complex SQL statement with multiple variables can be a frustrating task.
One approach to simplifying this is to use the Chr$ function to embed the internal quotation marks. The expression Chr$(34) can be used to represent a quotation mark in code. The following code example shows how to use the Chr$ function to specify the quotation marks that enclose the control value.
strSQL = "SELECT * FROM Orders WHERE ShipCity = " & Chr$(34) & Me!txtShipCity & Chr$(34)
Another approach to embedding a variable or control value in an SQL statement is to use a set of single quotation marks (') around the variable. The following code example shows this technique.
strSQL = "SELECT * FROM Orders WHERE ShipCity = " & "'" & Me!txtShipCity & "'"
You may prefer to use this technique if you are familiar with the SQL syntax supported by Microsoft SQL Server. SQL Server only supports delimiting string criteria with single quotation marks. You must use this syntax if you use an SQL pass-through query or using a SQL Server recordset in ActiveX Data Object (ADO) code.
This technique has one major drawback. It fails if the value that is stored in the variable contains an apostrophe.
To resolve this problem, you must double the number of single quotation marks inside the variable. The following function shows a method that can be used to address this problem.
Public Function FixSingleQuotes(varValue As Variant) As String
Const SINGLEQUOTE = "'"
HandleSingleQuotes = SINGLEQUOTE & _
Replace(varValue, SINGLEQUOTE, SINGLEQUOTE & SINGLEQUOTE) & _
SINGLEQUOTE
End Function
The FixSingleQuotes function uses the Replace function to replace embedded single quotation marks with two single quotation marks. If the value has no single quotation marks, the Replace function does not change the value. Before returning its result, the FixSingleQuotes function concatenates the leading and trailing apostrophes to the input value.
The following code example shows how to use the function while you are building an SQL statement. In the following code example, the strLastName variable contains the value to evaluate.
strSQL = "SELECT * FROM Orders WHERE LastName = " & HandleSingleQuotes(strLastName)
Using Date and Time Criteria
When you build an SQL statement that contains date or time criteria, you must enclose the date or time with the number sign (#) delimiter.
The following code example shows how to use a literal date in an SQL statement that is assigned to the strSQL variable.
strSQL = "SELECT * FROM Orders WHERE OrderDate >#3-1-2009#;"
The following code example shows how to use the value that is contained in the txtDate text box to specify the filter for the active form.
Me.Filter = "[OrderDate] >#" & Me!txtDate & "#"
Me.FilterOn = True
Using Numeric Criteria
Building an SQL statement that contains numeric criteria does not require you to use any delimiters.
The following code example shows how to use numeric criteria in an SQL statement that is assigned to the strSQL variable.
strSQL = "SELECT * FROM [Order Details] WHERE [Order ID] = 30"
The following code example shows how to use the value that is contained in the txtDate text box to specify numeric criteria.
strSQL = "SELECT * FROM [Order Details] WHERE [Order ID] =" & txtOrderID
Working with Long SQL Statements
As shown, SQL statements can be difficult to build correctly. When you are building long SQL statements, you may want to break them into multiple lines. Breaking an SQL statement into multiple lines can make it easier to read and maintain.
One method that you can use to break an SQL statement into multiple lines is to use the line-continuation character ( _). The line-continuation character is a space followed by an underscore (_). If you break the line within the SQL statement, you must include the concatenation operator (&) before the line-continuation character.
The following code example shows how to use the line-continuation character to concatenate a long SQL statement in a readable format.
strSQL = "SELECT FirstName, LastName, HireDate, FROM Employees " & _
"WHERE Country = '" & Me!cboCountry & "' " & _
"ORDER BY LastName"
Notice that the second and third lines of the statement were indented to make it easier to read.
A second method that you can use to make long SQL statements more readable is to build them one line of code at a time. The following code example shows how to do this.
strSQL = "SELECT FirstName, LastName, HireDate, FROM Employees "
strSQL = strSQL & "WHERE Country = '" & Me!cboCountry & "' "
strSQL = strSQL & "ORDER BY LastName"
The second and third lines of code concatenate more text to the same variable. One advantage to this method is that you can inspect the SQL statement as it is built.
Conclusion
Building SQL statements in VBA can be tricky. In this article, you learned how to build an SQL statement that integrates the value of a VBA variable or control that contains string, date, and numeric criteria.
Additional Resources
For more information about products and technologies mentioned in this article, see the following resources: