String functions and how to use them

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

With string functions, you can formulate expressions in Access that override text in a several forms. For example, you may want to present some of a serial number on a form. Or, you could have to join (concatenate) numerous strings together, like a last name and a first name. If you’re fairly new to expressions, see Build an expression.

Here’s a description of some of the more familiar string operations in Access, and the functions you would apply to run them:

To…Use the…For example…Results
Return characters from the beginning of a stringLeft function=Left([SerialNumber],2)If [SerialNumber] is “CD234”, the result is “CD”.
Return characters from the end of a stringRight function=Right([SerialNumber],3)If [SerialNumber] is “CD234”, the result is “234”.
Find the position of a character in a stringInStr function=InStr(1,[FirstName],”i”)If [FirstName] is “Colin”, the result is 4.
Return characters from the middle of a stringMid function=Mid([SerialNumber],2,2)If [SerialNumber] is “CD234”, the result is “D2”.
Trim leading or trailing spaces from a stringLTrim, RTrim, and Trim Functions=Trim([FirstName])If [FirstName] is “ Colin ”, the result is “Colin”.
Join two strings togetherPlus 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 themPlus 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 caseUCase function or LCase function=UCase([FirstName])If [FirstName] is “Colin”, the result is “COLIN”.
Determine the length of a stringLen 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 fastest way to join strings together. In a desktop database, you can even use the ampersand operator (&) for concatentation. In an Access app, you are required to use the plus sign (+).

There are countless more text-related functions in Access. A great way to learn more about them is to access the Expression Builder and browse through the function lists. The Expression Builder is accessible virtually anywhere you desire to construct an expression—typically there’s a small Build button that resembles this: Button image

To illustrate the Expression Builder, let’s enter it from the Control Source property on a form or view. Utilise either one of the steps below based on whether you’re using a desktop database or an Access web app.

Display the Expression Builder in a desktop database

  1. Enter a desktop database (.accdb).
  2. Press F11 to launch 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 choose Layout View. If you don’t have a form to work with, press Create > Form.
  4. Right-click a text box on the form, and select Properties.
  5. In the Property Sheet, choose All > Control Source and select the Build button Button image on the right side of the Control Source property box.

    The Build button in the Property Sheet.
  6. Below Expression Elements, expand the Functions node and choose Built-In Functions.
  7. Beneath Expression Categories, pick Text.
  8. Below Expression Values, pick the different functions and scan 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. Enter the web app in Access. If you’re viewing in the browser, choose Settings > Customize in Access.
  2. Select a table in the left column, then to the right of the tables list, pick a view name.

    Selecting a view in Access.
  3. Choose Edit, select a text box, and press the Data button that appears next to the text box.

    The Data button next to a Text Box control.
  4. Choose the Build button Button image to the right of the Control Source drop-down list.
  5. Below Expression Elements, expand the Functions node and select Built-In Functions.
  6. Below Expression Categories, select Text.
  7. Below Expression Values, pick 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.

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: