String functions and how to use them

With string functions, you can create expressions in Access that manipulate text in a variety of ways. For example, you might want to display only part of a serial number on a form. Or, you might need to join (concatenate) several strings together, such as a last name and a first name. If you're not savvy with expressions yet, see Build an expression.

Here's a listing of some of the more common string operations in Access, and the functions you would use to perform them:

To…	Use the…	For example…	Results
Return characters from the beginning of a string	Left function	=Left([SerialNumber],2)	If [SerialNumber] is "CD234", the result is "CD".
Return characters from the end of a string	Right function	=Right([SerialNumber],3)	If [SerialNumber] is "CD234", the result is "234".
Find the position of a character in a string	InStr function	=InStr(1,[FirstName],"i")	If [FirstName] is "Colin", the result is 4.
Return characters from the middle of a string	Mid function	=Mid([SerialNumber],2,2)	If [SerialNumber] is "CD234", the result is "D2".
Trim leading or trailing spaces from a string	LTrim, RTrim, and Trim Functions	=Trim([FirstName])	If [FirstName] is " Colin ", the result is "Colin".
Join two strings together	Plus sign (+) operator*	=[FirstName] + [LastName]	If [FirstName] is "Colin" and [LastName] is Wilcox, the result is "ColinWilcox"
Join two strings together with a space in between them	Plus sign (+) operator*	=[FirstName] + " " + [LastName]	If [FirstName] is "Colin" and [LastName] is Wilcox, the result is "Colin Wilcox"
Change the case of a string to upper or lower case	UCase function or LCase function	=UCase([FirstName])	If [FirstName] is "Colin", the result is "COLIN".
Determine the length of a string	Len function	=Len([FirstName])	If [FirstName] is "Colin", the result is 5.

* Okay, so it's not a function, it's an operator. However, it's the quickest way to join strings together. In a desktop database, you can also use the ampersand operator (&) for concatentation. In an Access app, you must use the plus sign (+).

There are many more text-related functions in Access. A good way to learn more about them is to open the Expression Builder and browse through the function lists. The Expression Builder is available almost anywhere you want to build an expression—usually there's a little Build button that looks like this:

To demonstrate the Expression Builder, let's open it from the Control Source property on a form or view. Use one of the procedures below depending on whether you're using a desktop database or an Access web app.

Display the Expression Builder in a desktop database

1. Open a desktop database (.accdb).

2. Press F11 to open the Navigation Pane, if it's not already open.

3. If you already have a form available, right-click it in the Navigation Pane and click Layout View. If you don't have a form to work with, click Create > Form.

4. Right-click a text box on the form, and click Properties.

5. In the Property Sheet, click All > Control Source and click the Build button on the right side of the Control Source property box.
   
   
   

6. Under Expression Elements, expand the Functions node and click Built-In Functions.

7. Under Expression Categories, click Text.

8. Under Expression Values, click the various functions and read the short descriptions at the bottom of the Expression Builder.

   Note:  Not all of these functions are available in all contexts; Access filters the list automatically depending on which ones work in each context.

Display the Expression Builder in an Access web app

1. Open the web app in Access. If you're viewing in the browser, click Settings > Customize in Access.

2. Click a table in the left column, then to the right of the tables list, click a view name.
   
   
   

3. Click Edit, click a text box, and click the Data button that appears next to the text box.
   
   
   

4. Click the Build button to the right of the Control Source drop-down list.

5. Under Expression Elements, expand the Functions node and click Built-In Functions.

6. Under Expression Categories, click Text.

7. Under Expression Values, click the various functions and read the short descriptions at the bottom of the Expression Builder.

Combine text functions for more flexibility

Some string functions have numeric arguments that, in some cases, you need to calculate each time you call the function. For example, the Left function takes a string and a number, as in =Left(SerialNumber, 2). This is great if you know you always need the left two characters, but what if the number of characters you need varies from item to item? Instead of just "hard coding" the number of characters, you can enter another function that calculates it.

Here's an example of serial numbers that each have a hyphen somewhere in the string. However, the position of the hyphen varies:

SerialNumber
3928-29993
23-9923
333-53234
3399940-444

If you only want to display the numbers to the left of the hyphen, you need to do a calculation each time to find out where the hyphen is. One option is to do something like this:

=Left([SerialNumber],InStr(1,[SerialNumber],"-")-1)

Instead of entering a number as the second argument of the Left function, we've plugged in the InStr function, which returns the position of the hyphen in the serial number. Subtract 1 from that value and you get the correct number of characters for the Left function to return. Seems a little complicated at first, but with a little experimentation you can combine two or more expressions to get the results you want.

For more information about using string functions, see Using string functions in your Access SQL queries.