In This Site

In This Section

The number one source for making your website sell!

In our last tutorial, Creating Calculated Fields in Microsoft Access Queries, you learned how to use calculated fields to extend the power of your Access queries. In this lesson, we'll examine the many text functions available to you.

Contents

LEFT, RIGHT and MID Functions
TRIM, LTRIM and RTRIM Functions
LCase and UCase Functions
Len Function
Replace Function
In String (InStr) Function
In String Reverse (InStrRev) Function
Splitting Text Fields

Using text criteria in your queries enables you to filter your data in many unique ways. I had to scour the web to find detailed descriptions of the many text functions available. For some reason, Microsoft chose not to document these in the help files that come installed with Access.

Text, or string functions as they are more commonly known, fall into numerous categories:

• Functions designed to find records that match certain text values at the beginning, end or middle of a text string.
• Check the case and convert it to a different one such as UPPER, LOWER or TITLE Case.
• Functions designed to count the number of characters in a string and perform a function based upon the results.
• Functions that will trim the string to limit it to a certain number of characters. You can trim from the beginning OR the end of the string.

You'll be amazed what you can do with text or string functions.

LEFT, RIGHT and MID Functions

In the first example, we'll return only certain parts of a string field. Open the Northwind Database in Microsoft Access. Now click on the query tab and select new. Add the Customers table to the query design window. Finally, drag the CustomerName field to the query grid. Your query should look like the following:

Now, in the second column Field, type the following:

NameLeft: Left([CompanyName],5)

This will tell it to create a new field called NameLeft which will take the CompanyName field and return only the first 5 characters.

The syntax is as follows: Left([FieldName],Number)

Your table will look like this image:

Let's now add a few other similar functions. In the third column type:

NameRight: Right([CompanyName],5)

In the fourth column add the text:

NameMid: Mid([CompanyName],4,5)

Your table will look like the following:

Your query will show:

• Column 1. The full company name
• Column 2. The leftmost five characters including spaces.
• Column 3. The rightmost five characters including spaces.
• Column 4. Five characters including spaces starting at character number four.

These functions can be used any time you need to return only part of a field name.

TRIM, LTRIM and RTRIM Functions

These functions are useful for removing leading or trailing blank spaces from fields. This is often a problem for fields like telephone numbers, email addresses or postal codes. Most often, TRIM will be used to remove both leading and trailing blank spaces.

The syntax is:

TRIM ( [FieldName] )
LTRIM ( [FieldName] )
RTRIM ( [FieldName] )

To use this in your queries, use the same process we used on the LEFT function to create a new field with the data limited. You would do this as follows:

NameLTrim: LTRIM([CompanyName])

This will create a new field called NameLTrim with leading spaces removed.

LCase and UCase Functions

If you wish to convert text strings to lower or upper case you can use the LCase or UCase functions.

The syntax is:

LCase ( [FieldName] )
UCase ( [FieldName] )

The following screen shot shows a query that displays the CompanyName field from the Customer's table in normal case, lower case and upper case.

Len Function

If you need a count of the number of characters in a field, then the Len function is perfect. It returns a numeric value equal to the number of characters and spaces.

The syntax is:

Len ( [FieldName] )

You can use the criteria field to limit the records to certain Len values. The following query will return only those Company names with a Len value of more than 10 and less than 15.

In datasheet view, you'll see the following:

Replace Function

If you want to replace one string with another, the Replace function will take care of it for you.

Syntax: Replace ( [FieldName] , String to find, String to replace with)

In this example:

NameSpace: Replace ( [CompanyName] , " " , "-" )

will replace spaces with the dash character. Your table would look like the following:

In String Function

Sometimes, it is important to be able to find a particular string within your field. For instance, you may want to split a name containing first and last names into two fields, one for FirstName and another for LastName. The first step is finding the space character that separates the two words.

Syntax: InStr ( Start position, [FieldName] , String to find, compare rule)

In this example:

NameSpace: InStr([English Name]," ") will create a new field called NameSpace that will return a numeric value indicating the position of the space. In the case of my name "Ward Cameron", the value would equal 5.

The Start position is optional. By default, it begins searching at the first character. Also the compare rule is optional. The two main options are 1, which does a binary comparison and 2 which does a text comparison. In most cases you can leave this blank.

In String Reverse Function

The same function can be used to search from the end of a string rather than the beginning. In this case, it is called the InStrRev Function. The syntax is the same:

Syntax: InStrRev ( Start position, [FieldName] , String to find, compare rule)

The options are the same as for the InStr function.

Splitting Text Fields

With a little ingenuity, these functions can be used to effectively split two fields. In our example above, the Name field should be split into FirstName and LastName. We'll do this in several steps.

First, we need to separate out the first name. To do this, we need to use two functions.

1. The InStr function will help us to find the numeric position of the space separating the first and last names.
2. The Left function will return only those characters to the left of the space.

Here is the syntax:

FirstName: Left([Name],InStr([Name]," "))

This will create a new field called FirstName. Breaking it down, we see the Left function with the following syntax: Left([Name],Number)

In this case, Number represents the position of the space character. The problem lies in the fact that we don't know its position. The solution is to replace "Number" with the InStr function to find the space. We use the expression: InStr([Name]," ")

Putting the two functions together returns the first names based upon using the space character to separate the two words contained in the field.

Step two involves breaking out the second name to create the LastName field. This step requires three functions:

1. We use InStrRev to find the space
2. The Len function helps us to determine the length of the field
3. We use the Right function to break out the last name.

Here is the syntax:

LastName: Right([Name],(Len([Name])-InStrRev([Name]," ")))

In pseudo code here is what the code does:

LastName: Right part of the field beginning at the space (Length of the Field - Distance from the end of the field where the space occurred)

Len([Name]) finds the length of the field. This value, when subtracted from the postion of the space will help us determine which part of the field to return. This is accomplished by Len - InStrRev.

For my name, "Ward Cameron", Len=12, InStrRev=8. So our formula would be Right([Name],(12-8)). The resulting LastName value will equal "Cameron", just the text to the right of the space.