How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statement, so here goes...

Delimiters

Before we start building a string in code, I think it's important to understand the concept of "delimiters" first. When you declare a String variable in VBA, the value you assign to it must be delimited with a double-quote character. For example:

strVariableName = "Some String Value"

That should be easy enough to understand, but the problem comes when building a string while concatenating field values into the string as well. Table fields have different data types and some data types need a specific delimiter when using them in code. The most common data types and their corresponding delimiters are as follows:

Data Type	Delimiter
Text	Double-quote (") or Single-quote (')
Date/Time	Octothorpe (#)

Here are some examples of their uses:

rs!CompanyName = "UtterAccess"

rs!CompanyName = 'UtterAccess'

rs!VisitDate = #4/29/2014#

rs!VisitTime = #10:30 AM#

As you can probably imagine, the problem we will try to tackle in this article is in how to build the string with Text data types within it since both String variables and Text data types use the same delimiters that usually causes some confusion during coding.

Building the string one step at a time

We are going to discuss an approach that was presented by another UtterAccess member when tackling this issue. First, let's look at the final version of the string we're trying to build. The goal will be to write code that will result in a string value that looks like this:

INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES ("O'Hara", "Scarlett", 1936, #4/28/1861#)

In our code, we will assume that each value to be inserted into the table are held in variables called strCustLastName, strCustFirstName, lngCustNo, and dteDOB. We will then insert these variables in their proper places to replace the hard values we used above.  Let's get started:

Step 1 - Naturally, the first thing to do is to delimit the whole value because it is a string, so let's add a double-quote at the beginning and at the end of our string:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES ("O'Hara", "Scarlett", 1936, #4/28/1861#)"

Step 2 - Next, we will replace the  hard values with the variables that represent each hard value:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (strCustLastName, strCustFirstName, lngCustNo, dteDOB)"

Step 3 - Now, the variables must be concatenated into the string so that VBA will then replace them with the values they represent when the code executes:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (" & strCustLastName & "," & strCustFirstName & ","
& lngCustNo & "," & dteDOB & ")"

Notice how we enclosed each segment of the concatenation with the double-quote delimiters because we are now trying to combine multiple strings together.

Step 4 - At this point, we'll need to add the proper delimiters for each data type for our variables. Remember that strCustLastName and strCustFirstName variables are for the CustLastName and CustFirstName fields respectively, which are Text fields and therefore need a double or a single quote for its delimiters. The lngCustNo variable represents the value for the CustNo field, which is a Number data type and therefore does not need any delimiter. And, the dteDOB variable is for a Date/Time field, which requires the octothorpe (hash mark):

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (""" & strCustLastName & """,""" & strCustFirstName & ""","
& lngCustNo & ",#" & dteDOB & "#)"

Notice how we actually used two double-quotes to delimit the Text data types (strCustLastName and strCustFirstName)? Now would be a good time to discuss some possible issues with Text data types when concatenating them into string values. If the hard values we expect will never contain a single-quote within it, then we could have just use a single-quote as delimiters and be done with it. However, remember that for our example, the strCustLastName variable actually represents the hard value "O'Hara." So, using a single-quote to delimit that variable will fail because the hard value also contains a single-quote. So to avoid that problem, we need to use a double-quote as a delimieter. But why use two of them? Well, if we used just one double-quote, then VBA will interpret it as the delimiter for our string (the whole thing that we're building) instead of just for the variable. So, we used two double-quotes to tell VBA to replace it with one double-quote when it interprets the code during execution.

Step 5 - Actually, that's it. We're done! There's no Step 5. However, one last item that I need to point out is that I didn't use any "continuation" character in the above code examples to reduce any confusion while learning the basic concept of building the string in code. So, the above code samples should be considered as one long line even if they were displayed as multiple lines above. If you wanted to break them down into multiple lines in your code using the continuation character, it might look something like this:

strSQL = "INSERT INTO tblCustomers (CustLastName, CustFirstName, " _
               & " CustNo, DOB) VALUES (""" & strCustLastName _
               & """,""" & strCustFirstName & """," & lngCustNo _
               & ",#" & dteDOB & "#)"

If you want to see the UtterAccess thread that inspired this article, click here. As usual, thanks for reading, and I would appreciate any comments or feedbacks.

Original post date: April 28, 2014