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:
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.
It is used to display the characters from the back.
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.
It is used to check whether a string or character is present in another string or not.
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.
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.
The function prints the elements from the middle of the string.
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.
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.
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.
The functions return the length of the string.
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.
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.
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:
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: