MS-Access / Getting Started

Text Functions

So far, all the functions you've seen have worked with numeric data. However, there's still a lot you can do with text. Overall, there are three ways you can manipulate text:

  • Join text. You can do things like combining several fields together into one field. This technique doesn't require a functioninstead, you can use the & operator.
  • Extract part of a text value. You may want just the first word in a title or the first 100 characters in a description.
  • Change the capitalization. You may want to show lowercase text in capitals, and vice versa.

Table-4 shows the most common functions people use with text.

Table-4. Functions for Text
UCase( )Capitalizes textUCase("Hi There")HI THERE
LCase( )Puts text in lowercaseLCase("Hi There")hi there
Left( )Takes the number of characters you indicate from the left sideLeft("Hi There", 2)Hi
Right( )Takes the number of characters you indicate from the right sideRight("Hi There", 5)There
Mid( )Takes a portion of the string starting at the position you indicate, and with the length you indicateMid("Hi There", 4, 2)Th
Trim( )Removes blank spaces from either side (or use LTrim( ) and RTrim( ) to trim spaces off just the left or right side)Trim("Hi There")Hi There
Len( )Counts the number of characters in a text valueLen("Hi There")8

Using these functions, you can create a calculated field that shows a portion of a long text value, or changes its capitalization. However, how you can use these functions in a filter expression may not be as obvious. You could create a filter condition that matches part of a text field, instead of the whole thing. Here's an example of a filter condition that selects records that start with Choco:

Left([ProductName], 5) = "Choco"

The Len( ) function's a bit of an oddity. It examines a text value and returns numeric information (in this case, the number of characters in the value, including all spaces, letters, numbers, and special characters). The Len( ) function isn't too useful in a simple calculated expression, because you'll rarely be interested in the number of letters in a text value. However, it does let you write some interesting filter conditions, including this one that grabs records with a Description of less than 15 characters (which probably could use some extra information):

Len(Description) < 15
[Previous] [Contents] [Next]