String functions and how to use them

With string functions, you can create expressions 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, 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: Button image

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 app. If you’re not sure what an Access app is, see What's New in Access 2013? or Create an Access 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 Button image on the right side of the Control Source property box.

    The Build button in the Property Sheet.

  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 App

  1. Open an 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.

    Selecting a view in Access.

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

    The Data button next to a Text Box control.

  4. Click the Build button Button image 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.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×