Using String Functions

String functions help you work with text data. For instance, you might want to replace one string with another or you might need to determine whether a string contains a specific character or a set of characters. In this case, and more, you'll rely on many of the string functions reviewed in this section.

The Asc Function

Oddly enough, string characters can be represented by an integer value, which can come in handy occasionally. What you're really working with are American National Standards Institute (ANSI) and American Standard Code for Information Interchange (ASCII) codes. When you need to work with that integer value, use the Asc function using the form

 Asc(string) 

where string is the literal string value or an expression that evaluates to a string that contains at least one valid character. A Null or zero-length string returns a runtime error. The returned value is an Integer data type from 0 to 255.

NOTE

The American National Standards Institute, or ANSI, is a private, nonprofit organization that administers and coordinates voluntary standardization in the United States.

The American Standard Code for Information Interchange, or ASCII, is a numerical representation of characters that computers can't process as is.

CAUTION

The Asc function returns the integer value for only the first character in a string, regardless of the total number of characters in the string. All characters other than the first are ignored.

Usually, string values must be properly delimited using the apostrophe or double quotation character. This isn't the case with Asc. For instance, the two functions both return the value 49:

 Asc("1") Asc(1) 

TIP

A delimiter is any character used to separate a value. For instance, VBA requires that you enclose a string in a set of apostrophe characters or double quotation marks, as follows: 'Harkins' or "Harkins". Dates must be properly delimited as well, using the pound sign (#).

The Chr Function

Just as Asc returns the ANSI value of a character, the Chr function returns the character equivalent of the ASCII character code. This simple function returns a variant of the String subtype and takes the form

 Chr(charactercode) 

where charactercode is a Long data type or expression that evaluates to an ASCII character. In the last section, you learned that 49 was the ANSI code for the value 1. On the flip side, use the Chr function to determine the character 49's corresponding character, which is of course, 1:

 Chr(49) 

TIP

Many string functions return a variant of the String subtype. To specifically force the function to return a String data type, use the $ form of the function. For instance, Chr returns a variant of the String subtype, but Chr$ returns a literal String value. The latter performs better in subsequent evaluations because VBA doesn't have to evaluate its data type before processing.

The Case Functions

There are two functions used to change letter case: LCase and UCase. Both functions take the form

 LCase(string) UCase(string) 

where string is a literal string value or an expression that returns a string value. The return value is a Variant String subtype, but both functions have a $ format alternative that return a literal string value. If string is Null, the function returns Null. LCase returns the input string converted to all lowercase letters, and UCase returns the input string converted to all uppercase letters.

The Len String Function

Use Len to count the number of characters in a string value. This function takes the form

 Len(string) 

where string is a literal string value or an expression that returns a string value. The character count is returned as a Long Integer unless string is Null. In the case of a Null string, the function returns Null.

The Left, Right, and Mid Functions

Three functions, Left, Right, and Mid, return a subset of characters from a string. All three return a Variant Long subtype and support a $ version to force a String data type.

The Left function takes the form

 Left(string, length) 

where string represents the string being evaluated and length indicates the number of characters to return, beginning with the first character in string. The Right function takes the same form as Left, but Right returns characters from the end of string.

The Mid function requires three arguments, using the form

 Mid(string, start[, length]) 

where start is a Variant Long subtype that determines where the count starts (from the left) and length is a Variant Long subtype that determines how many characters are counted.

In all three functions, if string is Null, the function returns Null. Figure 5.11 shows all three functions parsing different pieces of the same string.

Figure 5.11. Use Left, Right, and Mid to parse string values.

The Replace Function

You can use the Replace function to replace a character or a subset of characters with another set of characters. This function uses the form

 Replace(string, stringtoreplace, replacementstring[, start[, count[, compare]]]) 

where string is the complete String value, stringtoreplace is a string that represents the set of characters you plan to replace, and replacementstring is that String value that replaces stringtoreplace. There are three optional arguments: start identifies where Replace starts, count specifies how many characters in stringtoreplace to replace, and compare specifies the method used to compare stringtoreplace with string. Remember, a simple Replace can totally change the string. For instance, the following function changes the word block to black:

 Replace("block","o","a") 

The Space Function

Use Space to return a string consisting of a specific number of space characters as a Variant String subtype. Use the form

 Space(number) 

where number is an integer value that specifies the number of space characters to return. This function supports the $ format that forces the String data type.

The Split String Function

The Split function parses a single string into an array of delimited values. Basically, you're breaking the string into more than one piece. This function takes the form

 Split(expression[, delimiter[, count[, compare]]]) 

where expression, a String data type that represents the string to be parsed, is the only required argument. The optional delimiter argument is a Variant that specifies the delimiting character. Use the optional count argument to specify the number of strings to return. The last optional argument, compare, is a tad more complex, specifying the type of comparison method. The possible vbCompareMethod constants follow:

• vbBinaryCompare Comparisons are based on a sort order derived from the internal binary representations of the characters.

• vbTextCompare Comparisons are based on a case-insensitive text sort determined by your system's locale settings.

• vbDatabaseCompare This setting is unique to Access; the comparison is based on a sort order determined by the locale ID of the database.

The StrComp Function

Determine whether two strings are equal using the StrComp function. Use the form

 StrComp(string1, string2[, compare]) 

where string1 and string2 are strings or string expressions. The optional compare argument is one of the vbCompareMethod constants reviewed for the String function in the previous section. The returned value is one of the following Variant Integer subtype values:

• 1 string1 < string2

• 0 string1 = string2

• 1 string1 > string2

• Null string1 or string2 is Null

A string is less than another string if it comes before the second string in alphabetical order. StrComp comes in handy any time you want to sort arbitrary data alphabetically.

The Three Trimming Functions

Trim, LTrim, and RTrim remove leading or trailing spaces from a specific string. They all use the same form

 Trim(string) LTrim(string) RTrim(string) 

where string is a String data type or an expression that evaluates to a string. All three functions return a Variant String subtype and support the $ format for forcing the result to a String data type. If string is Null, all three functions return Null.

LTrim removes leading space characters from the beginning of string; RTrim removes leading space characters from the end. Trim removes unnecessary spaces from both the beginning and the end of string.

NOTE

If you're an experienced Access user, you might be wondering where the section on domain or aggregate functions is. You won't find one in this book because neither are part of the VBA object model. Domain and aggregate functions both have similar functions in that they evaluate the values in a field. Domain functions enable you to specify criteria to limit the records that are evaluated, but they're native to Access, not VBA. Aggregate functions are very similar in functionality to domain functions; however, you can't limit the records to be evaluated. In addition, aggregate functions are provided by SQL not by Access or VBA.

A String Function Example

Apostrophe characters within a string throw VBA for quite a loop. Unfortunately, they're everywhere. The sample database, TimeTrack.mdb, has a customer with an apostrophe character in its company name Bill's Auto Glass.

I don't go into the specific problems that these embedded apostrophes create, but do show you a simple function that deletes them. First, return to the VBE and the Chapter5 module (or any standard module). Then, do the following:

1. Enter the following function procedure into the module:
   
   

   
   

    Public Function StripApostrophe(str As String) As String   StripApostrophe = Replace(str, "'", "") End Function 

   
   

2. In the Immediate window, run the following statement:
   
   

   
   

    ?StripApostrophe("Bill's Auto Glass") 

   
   

Figure 5.12 shows the results. The function procedure removed the apostrophe character. Your users don't have to be burdened with too many data entry rules when you can clean up the values for them. I'm not suggesting that you actually change the stored value. Rather, you can use this function when a stored value doesn't conform to the requirements. You can manipulate the value in the form you need without changing the stored value.

Figure 5.12. Use Replace to remove a troublesome character.