String Operators

(Concatenation)

 

Prior to VB4, the plus sign (+) was used not only to add numbers, but also to concatenate strings together. The plus sign can still be used for concatenation in later versions of VB (4 thru 6) to maintain backward compatibility:

 

      strName = "John"

      strGreeting = "Hello, " + strName + ". How are you?"

      'strGreeting now contains the string "Hello, John. How are you?"

 

In VB4, the ampersand  (&) was introduced as the new concatenation operator, and it is the preferred operator to use for concatenation in the later versions of VB:

 

strGreeting = "Hello, " & strName & ". How are you?"

 

When you have many items to string together (such as when composing a long in-line SQL query), you'll find it useful to break up a logical line of code into several physical lines. To do so, each line to be continued must end with the line continuation character (an underscore preceded by a space):

 

        strSQL = "SELECT Field1, Field2, " _

               & "Field3, Field4, Fileld5 " _

               & "FROM MyTable " _

               & "WHERE Field1 = '" _

               & strMyValue _

               & "' AND Field4 <> '" _

               & strSomeOtherValue _

               & "' ORDER BY 1, 2, 3"

 

Although a line of code can be over 1,000 characters long, it is strongly recommended that you break up long lines of code with line continuation character. A good rule of thumb is to keep the length of a line of code no longer than what can be seen on the screen without scrolling (approximately 70 to 80 characters long). In VB6, up to 25 lines can be continued with the line continuation character (in versions 4 and 5, the limit was 10 lines; prior to version 4, line continuation was not available).

 

As an alternative to using a series of continued lines to concatenate the pieces of a long string together, you can have several individual lines of code that concatenate the target string variable to itself, as in the following example:

 

            strSQL = "SELECT Field1, Field2, "

            strSQL = strSQL & "Field3, Field4, Fileld5 "

            strSQL = strSQL & "FROM MyTable "

            strSQL = strSQL & "WHERE Field1 = '"

            strSQL = strSQL & strMyValue

            strSQL = strSQL & "' AND Field4 <> '"

            strSQL = strSQL & strSomeOtherValue

            strSQL = strSQL & "' ORDER BY 1, 2, 3"

 

 

More on the & and + Operators

 

("Everything you wanted to know about the '&' and '+' operators, but were afraid to ask", taken from the MSDN help.)

 

& Operator

 

Used to force string concatenation of two expressions.

Syntax:

result = expression1 & expression2

 

Part                  Description

result                Required; any String or Variant variable.

expression1       Required; any expression.

expression2       Required; any expression.

 
Remarks

If an expression is not a string, it is converted to a String variant. The data type of result is String if both expressions are string expressions; otherwise, result is a String variant. If both expressions are Null, result is Null. However, if only one expression is Null, that expression is treated as a zero-length string ("") when concatenated with the other expression. Any expression that is Empty is also treated as a zero-length string.

 

 

+ Operator

 

Remarks

When you use the + operator, you may not be able to determine whether addition or string concatenation will occur. Use the & operator for concatenation to eliminate ambiguity and provide self-documenting code.

 

If at least one expression is not a Variant, the following rules apply:

 

If

Then

Both expressions are numeric data types (Byte, Boolean, Integer, Long, Single,

Double, Date, Currency, or Decimal)

Add.

 

Both expressions are String

Concatenate.

One expression is a numeric data type and the other is any Variant except Null

Add.

One expression is a String and the other is any Variant except Null

Concatenate.

One expression is a numeric data type and the other is a String   

A Type mismatch error occurs.

Either expression is Null

result is Null.

 

 

If both expressions are Variant expressions, the following rules apply:

If

Then

Both Variant expressions are numeric

Add.

Both Variant expressions are strings

Concatenate.

One Variant expression is numeric and the other is a string

Add.

 

For simple arithmetic addition involving only expressions of numeric data types, the data type of result is usually the same as that of the most precise expression. The order of precision, from least to most precise, is Byte, Integer, Long, Single, Double, Currency, and Decimal. The following are exceptions to this order:

 

If

Then result is

A Single and a Long are added

a Double.

The data type of result is a Long, Single, or Date variant that overflows its legal range,

converted to a Double variant.

The data type of result is an Integer variant that overflows its legal range,

converted to a Long variant.

 

A Date is added to any data type,          

a Date.

 

If one or both expressions are Null expressions, result is Null. If both expressions are Empty, result is an Integer. However, if only one expression is Empty, the other expression is returned unchanged as result.

 

Note   The order of precision used by addition and subtraction is not the same as the order of precision used by multiplication.

 

This example uses the + operator to sum numbers. The + operator can also be used to concatenate strings. However, to eliminate ambiguity, you should use the & operator instead. If the components of an expression created with the + operator include both strings and numerics, the arithmetic result is assigned. If the components are exclusively strings, the strings are concatenated.

 

Dim MyNumber, Var1, Var2

MyNumber = 2 + 2              ' Returns 4.

MyNumber = 4257.04 + 98112    ' Returns 102369.04.

 

Var1 = "34": Var2 = 6         ' Initialize mixed variables.

MyNumber = Var1 + Var2        ' Returns 40.

 

Var1 = "34": Var2 = "6"       ' Initialize variables with strings.

MyNumber = Var1 + Var2        ' Returns "346" (string concatenation).