MS Access Substring Function

Microsoft Access Substring function is mainly to extract the characters in the string. As we all know, the Substring function is not existed in Access, both in query or VBA Code. So how to extract the characters? Ms Access has several other functions that equivalent with the Substring function:

 

MS Access Substring LEFT Function

Left function is used for extract the characters from the string started from the first characters in left. The syntax for Left function: Left (string, [number of characters])

Parameters Descriptions
String The word or text
[number of characters] the amount of characters to be taken

Example: Left (“Bouncing off the wall”, 8)
Result: “Bouncing”

 

MS Access Substring RIGHT Function

Right function extracts the characters from a string started from the characters located in right.
The syntax for Right function: Right (string, [number of characters])
Example: Right (“Bouncing off the wall”, 8)
Result: “the wall”

 

MS Access Substring MID Function

Mid Function extract some characters from string data located in the middle of the string.
The syntax for Mid function: Mid (string, [start position], [number of characters])

Read Also:  How to Solve Unrecognized Database Format Access 2013
Parameters Descriptions
[start position] The position to start extracting the characters

Example: Mid (“Bouncing off the wall”, 10, 3)
Result: “off”

 

MS Access Substring INSTR Function

Instr Function is quite different than 3 functions above. It has more parameters and different function, through the function is still one of the substring function usage. Instead of extracting the characters from a string, it will search the “substring” and returned the result as the position number of the substring.
The syntax for Instr Function: InStr ([start], string1, string2, [compare])

Parameters Descriptions
[start] is the expression to set the starting position for each search
string1 the full text that going to be searched
String2 the string or “substring” that being to be sought
[compare] determine the type comparison for the string

Example: InStr (3, “Bouncing off the wall”, “o”, 0)
Result: 10

To sum it all, look at this picture:

Ms Access Substring (1)

Ms Access Substring (1)

 

Example in the query:

Ms Access Substring (2)

Ms Access Substring (2)

In VBA code, the function will have not much changing. However, you will need to add the argument for the data type.

Related Access Database

  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1822 NKGmdbprmClJo-IZ9VIv1xYJp31FxTzt6PQeFP9muHzpQstpMlHgA6kRxG7oE3wjQd04mP-l6NYIXnJTsC_eNw 1c31b52247c847d6f42ce960b8f72d3c75c17639&uuid=&state=_BLhILn4SxNIvvL0W45KSic
  • ms access substr function


Access Database Tags: #ms access instr function #ms access remove characters from string #ms access string functions #ms access substring #ms access substring left function #ms access substring mid function #ms access substring right function #ms access trim function

MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top