
The tutorial today will discuss about Ms Access Instr function and how to use it. The Instr function is purposed for searching the substring in the string and return the position of the first occurrence of one string within another.
The Syntax for Ms Access Instr
function is:
InStr( [start], string1, string2, [compare] )
Parameters
[start]: is the expression to set the starting position for each search. It is optional parameter, if omitted the search by default will be start with the first character postions.
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. The value for [compare] parameter can be looked in this table.
Constant | Value | Description |
vbUseCompareOption | -1 | Performs a comparison using the setting of the Option Compare statement. |
vbBinaryCompare | Performs a binary comparison, which turn text into a sequence to compare. The sequence is case sensitive. | |
vbTextCompare | 1 | Performs a case insensitive textual comparison. |
vbDatabaseCompare | 2 | Performs a comparison based on information in your Access database (case insensitive). |
Note: [compare] is optional, but if it used, you have to use the [start] parameter too.
Result
# | Condition | Returns |
1. | string1 is zero-length | |
2. | string1 is Null | Null |
3. | string2 is zero-length | start |
4. | string2 is Null | Null |
5. | string2 is not found | |
6. | string2 is found within string1 | Position at which match is found |
7. | start > string2 |
Examples
1. InStr (“Access Templates dot com”, “A”) or InStr (1, “Access Templates dot com”, “a”)
Result: 1
2. InStr (1, “Access Templates dot com”, “t”, 0)
Result: 14
Because using the vbBinaryCompare, which make the function is case sensitive.
3. InStr (5, “Access Templates dot com”, “A”)
Result: 13
Because it is started in 5th position.
4. InStr (“Access Templates dot com”, “dot”)
Result: 18
5. InStr(“Access Templates dot com”, “x”)
Result: 0
No found string2 in string1, see result condition #5.
Example in Query:
Example in VBA code:
Dim RackPosition As Integer
RackPosition = InStr (10, “Access Templates dot com”, “t”)
In VBA code, you only have to add the arguments for determine the Data Type. Normally the Instr function return the data type as Long.
Related Access Database
- access instr function