14.1. Working with SQL Strings in VBA

To build SQL statements in VBA, you should usually load them into string variables by concatenating various phrases together. Some of the phrases will be exact SQL text that you will supply, while others will be the contents of variables in VBA or controls on forms or reports. When the SQL statement is complete, you'll be able to use it in queries, in the recordsource of forms or reports, or in the rowsource of combo boxes or list boxes. This will allow you to deliver power and flexibility in your Access applications.

14.1.1. Building SQL Strings with Quotes

The first thing to learn about building SQL statements in VBA is handling concatenation and quotes. They may seem simple, but many programmers have stared at VBA strings with multiple nested quotes and struggled to make them work.

Consider a SQL string that selects a record for a particular business from a table of businesses:

Select * From tblBusiness Where BusinessKey = 17

In actual usage, we need to replace the 17 in the above statement with the BusinessKey that the user is currently working with. To build this SQL statement in VBA, using the BusinessKey from the current form, you would use something like this:

strSQL = "Select * From tblBusiness Where BusinessKey = " _
& Me!BusinessKey

One reason this is so simple is that BusinessKey is a numeric value. In SQL, numeric values are just stated, without quotes around them. This is great for primary key values, which are often AutoNumbers (with Long Integer data types).

However, consider a SQL statement that selects businesses in a particular city:

Select * from tblBusiness Where BusinessCity = "Seattle"

This is where it starts to get complicated. As you can see, you need to wrap Seattle in quotes, because SQL expects them around text values. The VBA to create this statement, again assuming that BusinessCity is on the current form, is

strSQL = "Select * From tblBusiness Where BusinessCity = """ _
& Me!BusinessCity & """"

At first glance, all those quotes seem a little extreme. But if we break them down, they make sense. The first thing to remember is that in order to have a quote (") inside a string, you need to type two quotes in a row. This lets VBA know that you aren't closing the string with a quote—you actually want a quote inside the string.

So, the string

"Select * From tblBusiness Where BusinessCity = """

results in a string that contains

Select * From tblBusiness Where BusinessCity = "

Notice that last quote? This is a result of the two quotes after the equal sign (=) "collapsing" into just one quote. This idea of collapsing quotes in the interior of your strings is crucial to understanding how to build complex SQL strings in VBA. You may even want to print out your VBA code and circle the interior quote pairs with a pen. Each of these circles represents a quote that will be included inside your string.

Now, let's look at the rest of this simple example. After the first phrase (the one that ends with a quote), we need to tack on the value of BusinessCity (Seattle) and then finish it off with a final quote. Concatenating the BusinessCity is easy:

& Me!BusinessCity

But what about that final quote? Here's how it is added:

& """"

Yes, that's four quotes in a row. Remember, the interior pairs of quotes are collapsed into a quote inside the string. In this case, the result is a string containing merely one quote mark, which is exactly what we need at the end of Seattle in our final SQL string.

Select * from tblBusiness Where BusinessCity = "Seattle"

To summarize the topic of quote collapsing, remember what is happening when you see three or four quotes in a row.

Whenever you see three quotes in a row, you can be sure that one quote mark is being included at the beginning or end of some other text, like this:

"Select * From tblBusiness Where BusinessCity = """

And whenever you see four quotes in a row, you are seeing just one quote mark being concatenated to the string, as in this example:

Me!BusinessCity & """"

Now that you know how to build SQL strings with text, values from variables and forms, and double quotes, there's one little side topic we need to cover: The use of single quotes (') instead of double quotes (").

14.1.2. Using Single Quotes Instead of Double Quotes

Some programmers use a mixture of single quotes (') and double quotes (") when they are building SQL strings. This can be a good technique, because you don't need to do any "quote collapsing" as described previously. However, to some people it can be confusing to see the different types of quotes mixed together. It's a style thing—there isn't a right or wrong way.

VBA remembers what kind of quote started a string, so if you use the other kind in the middle of the string, it won't get confused and try to close the string. Access Wizards often use this technique to build SQL strings. For example, here's how the Access Wizard generates the WhereCondition phrase when you ask to open a form filtered to a specific value:

stLinkCriteria = "[City]=" & "'" & Me![txtCity] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Notice the mixture of single and double quotes in the string loaded into stLinkCriteria. The double quotes are used to indicate to VBA where the text phrases start and stop. The single quotes are built into the stLinkCriteria field itself. The reason the single quotes work is because the Access query processor recognizes either single or double quotes around text values. Therefore, the following two statements are identical to Access SQL:

Where City = 'Seattle'
Where City = "Seattle"

Also notice that the technique to build the string is a little more complicated than necessary. In order to generically handle either text or numeric values, the Access Wizard concatenates the first single quote separately. If you are building it yourself, you can tack the single quote right after the equals sign, like this:

stLinkCriteria = "[City]='" & Me![txtCity] & "'"

For the rest of the examples in this chapter, we'll use the "collapsing quotes" method described previously. This method will work whether you use all single quotes (') or all double quotes (").

14.1.3. Concatenating Long SQL Strings

In order to keep your VBA readable, you should break your long statements onto multiple lines. While this is true any time, it's especially helpful when building long SQL strings. If you do not break them into multiple VBA lines, you would need to scroll far to the right to read it all. Breaking up these long statements can be done in two ways: By building up the string variable in multiple steps or by using the VBA line continuation character.

Many programmers still use the "build up" method for storing long SQL strings into a string variable. It might just be habit left over from the days when there wasn't a line continuation character, or maybe they just like the way it looks. This method looks like this:

strSQL = "Select * From tblBusiness"
strSQL = strSQL & " Where BusinessCity = """ & Me!BusinessCity & """"
strSQL = strSQL & " And BusinessActiveFlag = True"

Notice how the second and third lines concatenate more text to the same variable, which is why we call it "building up" the string. This method has a slight advantage during debugging since you can see you string's value step-by-step as it is being built.

Another way to do this is to use the VBA line continuation character, which is a space and underscore together:

strSQL = "Select * From tblBusiness" & _
" Where BusinessCity = """ & Me!BusinessCity & """" & _
" And BusinessActiveFlag = True"

Some developers indent the subsequent lines for clarity:

strSQL = "Select * From tblBusiness" & _
    " Where BusinessCity = """ & Me!BusinessCity & """" & _
    " And BusinessActiveFlag = True"

This method runs the entire concatenation as one line in VBA, even though it is visually spread across multiple lines in your code.

Breaking your VBA onto multiple lines is another area that's really a style choice—all these methods work just fine. However, whichever method you choose, you should break the statement where it makes sense, starting each new VBA line with a keyword like Where, And, Or, Join, and so on so that others can read along more easily.

Also, notice that you need to be careful to add the extra spaces around the keywords like Where and And. If you don't, your words will run together in the final string, and the syntax will be incorrect and the SQL statement won't run. Many programmers add them to the beginning of each section of text instead of the end, so that they really stand out. Also, remember that extra spaces between words aren't a problem in SQL; they're ignored by both Access and SQL Server.

Now that we've covered how to use quotes and build long SQL strings in VBA, let's use them to enhance Access forms and reports.