Building SQL in VBA

BaldyWeb

A Tutorial on Building SQL in VBA

How do I construct an SQL statement in VBA?

Constructing an SQL statement in VBA can be tricky, because there are often several areas of Access needed to construct the string. For example, proper SQL syntax, proper VBA syntax, and proper referencing of controls are three common points. We will cover the basics of constructing SQL statements in this FAQ, and touch on some incidental points as well.

String concatenation
This is perhaps the most common problem area. You must include proper spacing and delimiting.

--Spacing--

Consider:

Public Function SQLTest()
Dim strSQL As String

strSQL = "SELECT * FROM tblMain" & _
                 "ORDER BY [TableID]"

CurrentDb.OpenRecordset strSQL

End Function

When attempting to execute this procedure, we will get a run-time error. To help identify the problem, modify the code slightly to print out the SQL string to the immediate window. This is the single most helpful debugging technique for troubleshooting SQL statements in VBA:

Public Function SQLTest()
Dim strSQL As String

strSQL = "SELECT * FROM tblMain" & _
           "ORDER BY [TableID]"

Debug.Print strSQL

CurrentDb.OpenRecordset strSQL

End Function

Now, take a look at the output (in the immediate window):
SELECT * FROM tblMainORDER BY [TableID]

Easy to see that you are missing a space between the table name and the ORDER BY clause. Simple enough to fix:

strSQL = "SELECT * FROM tblMain " & _
         "ORDER BY [TableID]"

Note the insertion of a space immediately after tblMain. This is a common mistake, especially when breaking out a SQL statement over multiple lines. Some like to put a space at the end of the line, some like to put a space at the beginning of the next line. This doesn't matter, just be consistent.

--Delimiting--

Certain types of data need certain types of delimiters to be handled properly. Numbers, strings, and dates are each handled differently. We will start with strings, as they are the most complicated:

Consider:

Public Function SQLTest()
Dim strSQL As String
Dim strMyString As String

strMyString = "jason"

strSQL = "SELECT * FROM tblMain " & _
           "WHERE [TextField] = strMyString"

Debug.Print strSQL

CurrentDb.OpenRecordset strSQL

End Function

When attempting to open this recordset we will get a runtime error. To see why, let's look at the resulting SQL statement:
SELECT * FROM tblMain WHERE [TextField] = strMyString

Of course, this is not what we want. Where is the string "jason" in the above? It is not there because we included the variable name strMyString inside the quotation marks, so the variable name was treated as literal name and was not evaluated for what it contained.

To get this value, we need to put the variable outside of the SQL string, so that VBA can evaluate the string and then include that evaluated value into the SQL string. Let's try:

strSQL = "SELECT * FROM tblMain " & _
         "WHERE [TextField] = " & strMyString

When attempting to open this recordset, we get the same error! Why? Well, look at the resulting SQL statement:
SELECT * FROM tblMain WHERE [TextField] = jason

The problem here is just a bit more subtle. In the above statement jason is not being treated as a string. We need to surround it with a delimiter that SQL knows means "a string". Like quotation marks (single or double). This is where it starts getting confusing:

strSQL = "SELECT * FROM tblMain " & _
         "WHERE [TextField] = " & Chr$(39) & strMyString & Chr$(39)

This is fine (for now). Look at the SQL statement:
SELECT * FROM tblMain WHERE [TextField] = 'jason'

Chr$(39) is the single quotation mark (or apostrophe).

Here's the thing: What if the string contains a delimiter? That is, what if the name was "O'Toole"?

strMyString = "O'Toole"

strSQL = "SELECT * FROM tblMain " & _
         "WHERE [TextField] = " & Chr$(39) & strMyString & Chr$(39)

Another error! Look at the resulting SQL statement:
SELECT * FROM tblMain WHERE [TextField] = 'O'Toole'

This is a problem of surprising complexity if you have to deal with strings that contain possible delimiters. You could delimit the string with double quotation marks:

strSQL = "SELECT * FROM tblMain " & _
         "WHERE [TextField] = " & Chr$(34) & strMyString & Chr$(34)

SELECT * FROM tblMain WHERE [TextField] = "O'Toole"

Chr$(34) is the double quotation mark.

This is ok. But what if the string itself contains a double quotation mark?

strMyString = Chr$(34) & "What now?" & Chr$(34)

SELECT * FROM tblMain WHERE [TextField] = ""What now?""

An error! What to do? Well, you could double up the delimiting quotation marks:

strSQL = "SELECT * FROM tblMain " & _
         "WHERE [TextField] = " & Chr$(34) & Chr$(34) & _
          strMyString & Chr$(34) & Chr$(34)

SELECT * FROM tblMain WHERE [TextField] = """What now?"""

This works, but isn't easy to read, and even worse fails if the string does not contain double quotation marks! The solution some like is to use to always use either single quotes or double quotes for delimiters. Then, use a function to "prepare" the string for quoting, which simply means going through the string and doubling the chosen delimiter. The doubled-up delimiter is now treated as a normal character. There is also more reading on this issue at the Knowledge Base: HOWTO: Query for Literal Special Characters in a Where Clause

You'll be glad to know that if you made it through all that, the hardest part is behind you! Dates are up next, and we're going to look at the right way to handle them, and introduce a few new points about SQL in VBA as incidental information:

Public Function SQLTest()
Dim strSQL As String
Dim dtmMyDate As Date
Dim rs As DAO.Recordset

dtmMyDate = "1/1/2000"

strSQL = "SELECT * FROM tblMain " & _
           "WHERE [DateField] = #" & dtmMyDate & "#"

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)

If Not (rs.EOF) Then
    Debug.Print rs![DateField]
Else
    Debug.Print "Nothing found"
End If

Set rs = Nothing
End Function

The output:
SELECT * FROM tblMain WHERE [DateField] = #1/1/2000#
1/1/2000

Easy enough. We use octothorpes to delimit dates. You can try the above without delimiters and string-style delimiters to see the results. It's good to do this so you can understand the error or invalid results in case you make a mistake in the future.

The incidental points: We're opening the recordset and storing it in the variable rs. We are using rs.EOF to verify we got back something. This would be True if there were no matches, and if there were no matches, the recordset would be empty, and then we would get an error trying to print out rs![DateField].

Non-USA Date Formats
Another tricky things to remember about dates is that Jet expects all dates to be in the format mm/dd/yyyy. It does not take any international date format settings in the Control Panel into account. This means if you are storing your dates in a format like dd.mm.yy , you still need to use the mm/dd/yyyy format for dates in SQL.

Finally, we saved the easiest for last -- numbers. Now numbers are real easy, because you don't need a delimiter for them:

Public Function SQLTest()
Dim strSQL As String
Dim lngMyNumber As Long
Dim rs As DAO.Recordset

lngMyNumber = 30

strSQL = "SELECT * FROM tblMain WHERE [NumberField] = " & lngMyNumber

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)

If Not (rs.EOF) Then
    Debug.Print rs![Textfield]
Else
    Debug.Print "Nothing found"
End If

Set rs = Nothing
End Function

SELECT * FROM tblMain WHERE [NumberField] = 30
tom

Really straight forward. The incidental point: note I am printing a different field than I used in my criteria. That's because I'm selecting all fields using the asterisk in the SELECT clause.

That's the basics of delimiting data for SQL statements. Next up, we will cover referencing values from form controls, and provide examples of more complex SQL statements to build a sample search form in the sample database below.

Building SQL, part 2

Search form sample db

JasonM

Home