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 text||UCase("Hi There")||HI THERE|
|LCase( )||Puts text in lowercase||LCase("Hi There")||hi there|
|Left( )||Takes the number of characters you indicate from the left side||Left("Hi There", 2)||Hi|
|Right( )||Takes the number of characters you indicate from the right side||Right("Hi There", 5)||There|
|Mid( )||Takes a portion of the string starting at the position you indicate, and with the length you indicate||Mid("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 value||Len("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