Text Functions in MS Access

The string functions in MS Access allow the user to create expressions. The expressions are used to perform manipulation using text.

For Example: If you have a mobile phone number that includes a country code and a 10-digit number in a single string. If the user only wants to display the 10-digit number on a form. Or, if the user wants to combine several strings into a single string.

If you are uncomfortable with using expressions then you can begin with Build an expression.

Now, let us discuss some of the most commonly used string functions in MS Access.

Special String Functions

We have mentioned some of the most used string functions along with their syntax and working:

Left Function

The function displays the characters in the string from the beginning.

The function requires two arguments:

String: The first argument in the left function specifies the string whose characters from the left are to be printed.

Length: The numeric argument in the function specifies how many characters from the left are to be printed. If the length entered is zero, it will display an empty string(""). If the value is more than or equal to the number of characters in the string, it displays the complete string.

For Example, let us consider a string OrderID = "IND2020". The first three characters represent the country. If the user only wants those three characters. Then the user can implement the left function as:

This will return "IND" as result.

Right Function

It is used to display the characters from the back.

Syntax:

The function requires two arguments.

String: The first argument in the right function specifies the string whose characters from the back are to be printed.

Length: The numeric argument in the function specifies how many characters from the right are to be printed. If the length entered is zero, it will display an empty string(""). If the value is more than or equal to the number of characters in the string, it displays the complete string.

For Example, let us consider a string OrderID = "IND2020". The last four characters represent the manufacturing year. If the user only wants those four characters. Then the user can implement the right function as:

This will return "2020" as result.

Instr Function

It is used to check whether a string or character is present in another string or not.

Syntax:

The function requires two arguments. Start and Compare are two optional parameters that can be passed in the Instr function.

Start: It is an optional parameter passed at the beginning of the function. It contains a numeric value that specifies the position where the search will begin. The function will return an error if the user enters the NULL as the starting value. If the search is not passed in the function, it begins from the first element.

Note: It is necessary to pass start if you add compare in the function.

String1: It is necessary to pass a string as the parameter. The first string represents the string in which we will perform the search for the character.

String2: The second string contains the character we are searching for in String1.

Compare: This is also an optional argument. It specifies the rule for performing the comparison. It determines the type of comparison performed on the string. The user needs to pass a valid LocaleID in the comparison to use the locale-specific rules. If compare is not passed, then the comparison type will be determined by the OptionCompare.

Return Value

0: The Instr function will return 0 in the following cases:

I) If the length of the String1 is zero.

II) If the String1 does not contain String2.

III) If the value in the start is more than the length of the String2.

NULL: The function will return NULL as the result if:

I) The String1 is NULL.

II) The String2 to be searched in the String1 is NULL.

Position at which match is found: If the String2 is found in the String1. It will return the position from where String2 begins in String1.

Mid Function

The function prints the elements from the middle of the string.

Syntax:

The following three parameters are passed in the mid function:

String: It determines the string for which we have to print the element from the middle.

Start: It points to the character position from where the taken part will start.

Length: It is an optional parameter. It is used to specify the number of middle characters returned by the function.

Note: The user can apply the MidB function for the strings containing data in bytes. This function does not take several characters as an input parameter; rather, it takes the number of bytes from the user.

For example, if the String1 "IND2020" is used on using the following expression: Mid([String1],2,2)

Then, the function will return "ND".

LTrim, RTrim and Trim Functions

These three functions are used to trim the string. They enable the user to remove any unnecessary spaces from the string.

LTrim: It removes the trailing spaces from the string.

RTrim: It removes the following spaces from the string.

Trim: It removes both trailing and following spaces from the string.

Syntax:

It only requires one parameter to perform the function.

String: The string from which you want to remove trailing or following spaces.

For Example: If the Name string is "Sanidhiya", then on using Trim([Name]), the function will return "Sanidhiya".

UCase and LCase Function

These two functions are used to change the case of the characters in the string.

UCase: The function converts all the lowercase characters.

LCase: The function converts all the uppercase characters.

Syntax:

It only requires one parameter to perform the function.

String: It is the string for which you can change the case character in the string.

For Example: If the Name String is "AMan", then on using the following commands:

UCase([Name]): It will return "AMAN" as a result.

LCase([Name]): It will return "aman" as the result.

Len Function

The functions return the length of the string.

Syntax:

String: The string whose length the user wants to determine.

For Example: If the user wants to calculate the length of the string Name that contains "Harsh", then the user can use the following function Len([Name]), and it will return the value 5.

+ operator

Sometimes, the '+' operator is also regarded as a string function, but actually, it is not a function. However, the fastest method allows the user to combine two strings into a single string. When working on the desktop database, the user can also use the ampersand operator to perform the concatenation of the strings. But in MS Access, you can use the '+'operator to concatenate the string.

Using Expression Builder in MS Access

There are several more string-related functions in MS Access. In order to learn more about the string functions in MS Access, the best method is to open the Expression Builder. It contains a list that states all the functions, and the user can browse among them. It is used to build an expression in the Access.

You can use the Build button, which enables the user to Expression Builder.

If you want to implement the Expression Builder, to open the Expression Builder, let us use the Control Source property. It is available on the form or the View.

The Expression Builder's steps differ for both the desktop database and the Access Web application. So, use the procedure mentioned below, depending on your application.

1. To Open the Expression Builder in a Desktop Database
2. First, open the desktop database (.db) where you want to implement the Expression Builder.
3. Once you have opened the database, open the Navigation Pane. You can use the F11 key. It is the shortcut for opening the Navigation Pane if it is not already opened when you use the desktop database.
4. If the user already has a form available, then the user is required to right-click in the Navigation Pane. From the Pane, click on the Layout View.
5. Follow the given steps if the user does not have a form. Click Create from it and select the form.
6. A new form will appear. Click on the text box in the form and Open its properties. The property sheet will appear. In the sheet, click on All and open the Control Source. Click on the Build button. It is located on the right side of the Control Source property box.
7. Once you click the Build button, see the Control Source property box.
8. In the menu of Expression Elements, there is an option for Functions. Click on it.
9. Then select the Built-in function.
10. From the categories, click text.
11. In the Expression Values, there is a list of various functions. There is a short description below the Expression Builder.

Note: You cannot implement all the functions in all contexts. The user is not required to be concerned about what functions to implement with the data. MS Access will automatically filter the list depending upon the functions that can be implemented.

Follow the given steps to open the Expression Builder in the MS Access web application:

1. Open the MS Access web application in your browser.
2. Now click on Settings and then click on Customize in Access.
3. Select the table in the left column, then click on View, which is located to the right of the table.
4. Click on Edit, then select the text box. There will be a Data button that will be available next to the text box.
5. Click the next button on the right side of the Control Source. It is a drop-down menu.
6. From the menu in the section for Expression Elements, click on the Functions.
7. From the Functions, select the Built-in Functions.
8. From the Expression categories, click on text. From the list of functions, select the functions based on the description and the task you want to perform. The description is located at the bottom of the Expression Builder.
9. You can combine text functions, adding more flexibility to the function.
10. Certain string functions also require numerical parameters to perform the task. You may require to modify or compute the value for the numerical argument every time you call that function.

For example, the Left function requires a string input and a number. The Left function print the last two characters of the string is Left(SerialNo, 2).

It will work every time when you want to access the last two characters of the string, but if you want to change the number of characters every time you have to implement the function. Instead of using hardcore coding to determine the value of the numerical argument, you can implement another function to compute the value for the argument.

Here the string parameter is the SerialNo that has hyphens in the strings. The position of the hyphen is different in the SerialNo, and if the task is to print all the left characters of the string, then you will have to compute the value to determine the location of the hyphen in the string. Instead of using the coding, you can simply use an In-Built function that will compute the value for you:

In the above example, we have not used a numerical value to input as the numerical argument for the Left function. We have used an Instr function which will return the numerical value that is the position of the hyphen in that particular string. Subtract 1 from the number to get the index of the character that the Left function will return.

It may seem a little complicated to use a function to compute another function's parameter. But after implementing such expressions several times, it will become more understandable.

SQL View in MS Access

When you are implementing the SQL Queries in MS Access, there will be instances where the user would want to manipulate the data that is stored in the columns rather than just returning the value of the column.

To open the SQL View in the MS Access database, follow the given steps:

1. Right-click on the Query tab located on the left side of the menu.
2. In the Query tab, select the icon for Design.
3. Select the table or tables for which you want the query and add the tables to the query design view. After adding the tables, close the dialogue box.
4. This will add the SQL Query Design for the selected tables.