VBA-(SQL)-String Tutorial

A topic that beginners in Access- and VBA-Programming are frequently struggling with is to define strings inside of VBA code. As long as the strings are visually displayed somewhere in the UI, it is fairly easy to find errors and correct them. If the string is processed technically, like an SQL String, it is not as easy to visually spot an error right away. You will only get a more or less descriptive error message. As a beginner to programming, you are having a hard time figuring out the cause for the error and correcting it.

First, I would like to show how to achieve the desired result with concatenating variables and functions, especially for SQL Strings executed by the Jet-Ace database engine.

Furthermore, I would like to illustrate how to prevent errors by using well structured, readable code from the start. And I would like to show you how to use visual output to analyze and fix errors even here.

The main medium to convey this topic is video tutorial showing all the above. So, this text is a summary and kind of an index to the video.

If this topic is new to you altogether, I recommend you start reading the text or watch the video from the beginning. If you rather want only to deepen your knowledge on one particular sub-topic, there is the following index of the text and the corresponding video segment.

Table of Contents / Shortcuts

General String Operations

Dim aStringVariable As String aStringVariable = "This is the text of the string"

If you want to include quotation marks in your string, you need to double them. Otherwise, the VBA compiler will not recognize that they should not end the string.

aStringVariable = "The word ""quotes"" is in quotes."

If you want to embed the (return) values of function calls or variables, you need to break up the string in several parts and concatenate them with those function calls and variable names. The parts of the whole string should be enclosed with quotes and concatenated with the ampersand symbol.

aStringVariable = "The current date is " & Date & " and here continues some text..."

Long text that are assigned to a string in VBA directly usually result in pretty long lines in the code. The long lines are hamper you reading the code because you need to scroll horizontally to read them completely.

To improve the clarity and readability of your code, you should break up long text literals in VBA to several lines. Generally, you add an underscore at the end of the line to indicate that the current instruction is going to continue on the next line. With strings, in particular, you need to end the current string part with quotation marks on the end of the line and add the ampersand to concatenate this part with the next part in the following line.

aStringVariable = "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. " & _ "Maecenas porttitor congue massa. Fusce posuere, magna sed " & _ "pulvinar ultricies, purus lectus malesuada libero, sit amet " & _ "commodo magna eros quis urna."

This approach will only put the line break into the VBA code in the editor. The resulting string value in the variable will not contain any line break. If you want to add a line break to the string value, you need to use the VBA constant vbCrLf (CR = Carriage Return, LF = Line Feed).

This constant will be concatenated with the other string parts using the same approach as illustrated for variables.

As an alternative, you can use the function Chr to generate the line break from the ASCII-Codes of Carriage Return (CR = 13) and Line Feed (LF = 10). Within the VBA environment this is not as readable and needs some compute time, so I would rather use the constant there.

Within expressions used in queries or the Access UI (forms and reports), you cannot use VBA constants. So, there the option to use the function is a sensible alternative.

SQL-Strings

Before you start building SQL Strings in the VBA-Editor, you should be familiar with the fundamental techniques outlined above. You’re going to need them all the time to write correct and readable strings in your code.

General Recommendation

In general, I recommend not pass any concatenated string directly to a procedure call or to assign it to a property. Rather declare a string variable and assign the concatenated string to that variable first. It does not make a difference in the end result. However, it is very easy then to use Debug.Print to output the resulting string to the Immediate Pane or a message box. That is a huge help to check the resulting string and to detect any errors in your string building logic.

SQL-Strings with Integer Numbers

If you want to use integer numbers in your SQL string you can just write the values into the string. When using integer variables, you need to concatenate them with the string, as shown above. This will result in the integer values being inserted into the string.

Dim customerId As Long Dim sql As String customerId = 4 sql = "SELECT * FROM tblOrder WHERE CustomerId = " & customerId

SQL-Strings with Text Literals

Using text values in SQL strings is a tiny bit more complicated. In SQL all text literals need to be enclosed in delimiters to separate them from keywords and identifiers in the strings. All the same as in VBA itself.

In Jet/Ace-(Access)-SQL you can delimit text literals with common quotes. - But remember, to add a quote to the string, you need to double it in the text you type in VBA.

Dim sql As String sql = "SELECT * FROM tblOrder WHERE OrderStatus = ""In Progress"""

As long as you deal with constant values directly written to your string, that is a somewhat useable approach. As soon as you concatenate the values from string variables to your SQL it becomes rather hard to read and understand.

Dim sql As String Dim orderStatus As String orderStatus = "In Progress" sql = "SELECT * FROM tblOrder WHERE OrderStatus = """ & orderStatus & """"

Actually, according to the SQL standard, the text delimiter in SQL is not our regular double quotation mark, but the single quotation mark. If we use that instead double quotes to delimit our string literals, it becomes much clearer which one of those is supposed to be a delimiter within the string and which one is a delimiter of the string in VBA.

Dim sql As String Dim orderStatus As String orderStatus = "In Progress" sql = "SELECT * FROM tblOrder WHERE OrderStatus = '" & orderStatus & "'"

SQL Strings with Date Values

Similar to text values, date literals need to be enclosed in delimiters in SQL strings. Other than with text literals we use the hash sign (#) as delimiter for date values.

In addition to that, you need to consider that the Jet-/Ace-database-engine will only understand date values in the US-American (mm/dd/yyyy) or ISO-International (yyyy-mm-dd) date format.

To account for that, we need to use the Format-Function to explicitly format date values in one of these formats before they are concatenated with our SQL string. For my eyes, the ISO date format is much more intuitively readable, so I will limit my example to that one.

Dim sql As String Dim firstDayCurrentMonth As Date firstDayCurrentMonth = DateSerial(Year(Date), Month(Date), 1) sql = "SELECT * FROM tblOrder WHERE OrderDate >= #" & Format(firstDayCurrentMonth, "yyyy-mm-dd") & "#"

Even if you happen to live in a region where the US date format is the default, I strongly advise you to still use the explicit formatting shown above. Otherwise, your application is unusable for user in any region using another date format.

SQL Strings with Decimal Numbers

It would be logical to assume that decimal number can be treated the same as integer numbers. - Unfortunately, the decimal symbol might get in the way.

Whenever a decimal number is converted to text, as it is when it is concatenated with a string, the decimal sign from Window’s Regional Settings will be used automatically. Depending on these settings, this might be a dot or a comma.

The Jet-/Ace-database-engine will only interpret a dot as a valid decimal symbol. The comma is designated to separate different values and expressions in SQL. It will cause an error if there is a comma within your decimal number.

You can use the Str function to achieve an explicit formatting of decimal numbers with the dot as decimal symbol.

Dim sql As String Dim orderTotal As Currency orderTotal = 119.99 sql = "SELECT * FROM tblCustomer WHERE TotalOrderValue >= " & Str(orderTotal)

And once again, as with the dates, I strongly recommend you use this explicit formatting even when your regional settings result in the dot being used as the decimal separator by default. - You never know for sure what your user’s settings will be.

Formatting Long SQL Strings in VBA

You can significantly improve the readability of long (longer than visible in one line without scrolling) SQL statements by splitting the string in VBA over multiple lines.

For best readability, you should particularly insert line breaks wherever a new clause of the SQL begins. I.e. you start (at least) a new line at the beginning of the FROM clause, and JOIN Expression, the WHERE clause, and, if present the GROUP BY and ORDER BY clauses as well.

Dim sql As String sql = "SELECT tblCustomer.CustomerId, tblCustomer.CustomerName, tblCustomer.CustomerZipCode, " & _ " tblCustomer.DateOfBirth, tblCustomer.TotalOrderValue, tblCustomer.LastOrderTime, " & _ " tblOrder.OrderId, tblOrder.OrderDate, tblOrder.OrderStatus " & _ " FROM tblCustomer " & _ " INNER JOIN tblOrder " & _ " ON tblCustomer.CustomerId = tblOrder.CustomerId " & _ " WHERE tblCustomer.CustomerId = 4 " & _ " AND TotalOrderValue > 100;"

While doing that, you should pay particular attention to add a blank between the last item on one line and the first item on the next line to separate those from another. - Remember: These line breaks are only in the source code, not in the resulting string.

It doesn’t do any harm if there is more than one blank in-between. So, it have taken to write blanks at the end as well as the beginning of any line.

Addendum / Frequently Asked Questions

The content of my video is covered already. But since publishing the video, I received some questions multiple times. They do not concern the very core topic of building (SQL) strings, so I did not cover them in the first place. Still, as they are closely related I’d like to answer them here.

Blanks and Special Characters in Column and Table Names

You are trying to adapt my SQL sample with your database, but you are getting error messages such as…

A possible explanation for these problems are blanks or special characters, like + - & * /, in your field or table names.

In almost all programming languages, including VBA and SQL, only continuous sequences of letters, numbers and the underscore will be recognized as identifiers for objects or variables. The above mentioned special characters are operators in SQL and the blank separates different identifiers.

When you create new tables, please remember not to use any of those in the field names or the table names. These kind of names have no benefit and require extra work and increase the risk of errors in your code. Column and table names are relevant to developers and the database engine only, not to end users who might like a “pretty” name. To display palatable column headers to your users, use the caption property of column names or, even better, use label controls in forms and reports.

If you already created an application with lots of table and field names with blanks and special characters, all is not lost. Fortunately, you do not need to go back and change all those names.

In SQL you can enclose table, query, or column names in square brackets to make them identifiable as coherent names to the database engine.

Dim sql As String sql = "SELECT [Customer Name] FROM [tblCustomer Blanks] "

Delimiters in Text Literals

You have got a text field in your table and want to query to query it using the where condition of a query. However, the criteria values themselves may contain the text delimiter, the single quote. This would be the case when searching for the name O'Brien.

If you use such a text literal inside your SQL statement it will cause a syntax error.

The core of this problem is the same as I explained at the beginning of the video with the “normal” double quotes inside the simple VBA string. The query parser sees the single quote inside the literal and considers it to be the end of the literal. The remaining part of the literal is then syntactically incorrect.

The solution is the same, as for the original problem. The single quote inside the text literal needs to be doubled to be treated as just one single quote inside the literal.

Dim sql As String sql = "SELECT * FROM tblCustomer WHERE CustomerName = 'Martha O''Brian' "

In the context of an application, this duplication of any single quote needs to happen automatically. You can achieve this by using the Replace function.

Dim sql As String Dim customerName As String customerName = "Martha O'Brian" customerName = Replace(customerName, "'", "''") sql = "SELECT * FROM tblCustomer WHERE CustomerName = '" & customerName & "' "

Occasionally I see the recommendation to go back to using use double quotes instead of single quotes as delimiter for text literals.

That is not a real solution, though. It just substitutes the problem. If you need to query for a name containing double quotes you would have the same problem again. And if you ever happen to encounter a criteria containing both, like O'Brian’s Pub “The bloody bucket” you are out of options. - By just sticking to the single quote delimiter and replacing any occurrence of it inside the literal with two single quotes will still work then.

Another, even better, solution would be to use parameters inside the SQL query instead of embedded literal values in the string. - But that’s a topic for another time.

Download of the Sample Database

I uploaded the sample database, I used in the video and which contains the code samples used in this text as a reference. You can download it here: StringBuildingDemo_v2.zip

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

Benefits of the newsletter subscription

VBA-(SQL)-String Video-Tutorial for Beginner