Extract a part of a text value - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

Extract a part of a text value

Applies to: Microsoft Office Access 2003

The following table lists examples of expressions that return a part of a string.

You can use these expressions in calculated controls (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.).

Expression	Description
=Left([ProductName], 1)	Displays the first character of the value of the ProductName field.
=Right([AssetCode], 2)	Displays the last 2 characters of the value of the AssetCode field.
=Mid([MyString], 18, 4)	Displays "Emma," if MyString contains the value "Congratulations, Emma."

You can use these expressions in a calculated field in a query.

Expression	Description
ProductInitial: Left([ProductName], 1)	Displays in the ProductInitial field the first character of the value in the ProductName field.
TypeCode: Right([AssetCode], 2)	Displays in the TypeCode field the last two characters of the value in the AssetCode field.
AreaCode: Mid([Phone], 2, 3)	Displays in the AreaCode field the three characters starting with the second character of the value in the Phone field.