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([SerialNumber],2) |
If [SerialNumber] is “CD234”, the result is “CD”. |
|
|
Return characters from the end of a string |
=Right([SerialNumber],3) |
If [SerialNumber] is “CD234”, the result is “234”. |
|
|
Find the position of a character in a string |
=InStr(1,[FirstName],"i") |
If [FirstName] is “Colin”, the result is 4. |
|
|
Return characters from the middle of a string |
=Mid([SerialNumber],2,2) |
If [SerialNumber] is “CD234”, the result is “D2”. |
|
|
Trim leading or trailing spaces from a string |
=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([FirstName]) |
If [FirstName] is “Colin”, the result is “COLIN”. |
|
|
Determine the length of a string |
=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.
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
-
Open a desktop database (.accdb).
-
Press F11 to open the Navigation Pane, if it’s not already open.
-
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.
-
Right-click a text box on the form, and click Properties.
-
In the Property Sheet, click All > Control Source and click the Build button
-
Under Expression Elements, expand the Functions node and click Built-In Functions.
-
Under Expression Categories, click Text.
-
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.
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.
