Access Database Design and Programming, Second Edition

VBA String Functions

Here are a handful of useful functions that apply to strings (both constants and variables):

The Len function

The Len function returns the length of a string, that is, the number of characters in the string. Thus, the code:

Len("January Invoice")

returns the number 15.

The UCase and LCase functions

These functions return an all uppercase or all lowercase version of the string argument. The syntax is:

UCase(string)
LCase(string)

For instance,

MsgBox UCase("Donna")

will display the string DONNA.

The Left, Right and Mid functions

These functions return a portion of a string. In particular:

Left(string, number)

returns the leftmost number characters in string, and:

Right(string, number)

returns the rightmost number characters in string. For instance:

MsgBox Right("Donna Smith", 5)

displays the string Smith.

The syntax for Mid is:

Mid(string, start, length)

This function returns the first length number of characters of string, starting at character number start. For instance:

Mid("Library.xls",9,3)

returns the string xls. If the length parameter is missing, as in:

Mid("Library.xls",9)

the function will return the rest of the string, starting at start.

The InStr function

The syntax for this very useful function is:

InStr(Start, StringToSearch, StringToFind)

The return value is the position, starting at Start, of the first occurrence of StringToFind within StringToSearch. If Start is missing, then the function starts searching at the beginning of StringToSearch. For instance:

MsgBox ...