Using string manipulation functions

Applies to
Microsoft Access 2000

You can use functions in your expressions to count characters, return characters, and determine the location of one string within another. By using the functions together, you can manipulate text strings in countless ways. As an example of the flexibility you gain by using functions in expressions, this article shows you how to separate a text string into two strings.

The string manipulation functions that you can use in Access include Len, Left, Right, and InStr.

For more information on other string functions, use the table of contents in Access 2000 Help. Click the Contents tab in the Access Help window, click the expand indicator next to Programming Information, expand Microsoft Access Visual Basic Reference, and then expand Functions. From the Functions Reference topic, you can navigate to all VBA function topics.

For more examples of expressions that manipulate strings, type examples of expressions in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

The Len Function

Len(string) returns the number of characters in the string. You can specify the string itself in quotation marks, or you can specify the field that contains the text string.

Expression Result
Len("Northwind") 9
Len("Robert King") 11
Len([LastName]) Depends on the contents of the LastName field. For example, if you are referring to the first record in the Employees table in the Northwind sample database, the value of the LastName field is Davolio, and the expression returns 7.

The Left Function

Left(string, length) returns a specified number of characters from the left side of a string. The first argument, string, specifies the text string, or the field containing the string, that you want to extract the result from. The second argument, length, specifies the number of characters that you want to return.

Expression Result
Left("Northwind", 5) North
Left("Robert King", 6) Robert

The Right Function

Right(string, length) returns a specified number of characters from the right side of a string. String specifies the text string, or the field containing the string, that you want to extract the result from; length specifies the number of characters that you want to return.

Expression Result
Right("Northwind",4) wind
Right("Robert King",4) King

The InStr Function

InStr([start, ]string1, string2[, compare]) returns a number specifying the position of the first occurrence of one string within another.

  • The first argument, start, is optional, and it is a number that specifies the position of the character where you want to begin searching. For example, if start is 3, the search begins at the third letter in the string. If you omit this argument, the search begins at the first character in the string.
  • The second argument, string1, is the string, or the field containing the string, that you are searching in.
  • The third argument, string2, is the string, or the field containing the string, that you are searching for.
  • The fourth argument, compare, is optional, and it determines how the InStr function compares string1 and string2. The default comparison type is binary and therefore case-sensitive.
Expression Result
InStr(1, "Northwind", "r") 3
InStr("Northwind", "r") 3
InStr("Northwind", "x") 0
InStr("Northwind","wind") 6
InStr("Robert King", " ") 7