Vba Option Compare Database Expected Text Or Binary

Visual Basic `InStr`

InStr is a powerful VB string part. It's the perfect way to search and test strings in robust Visual Basic 6.0 applications. This article shows how to call InStr to perform advanced text processing tasks rapidly. The commodity also discusses case insensitivity, vbTextCompare, Selection Compare Text and InStr related bugs.

VB6 statements and constants in this article: InStr, InStrB, InStrRev, Choice Compare, vbBinaryCompare, vbTextCompare.

InStr syntax

InStr returns the get-go occurrence of a string inside another string.

          InStr([start,]            string1,            string2            [,            compare])

start

Optional. Grapheme position where the search starts. If omitted, the search starts at the first of string1, position 1. Valid input values: 1 to Len(string1)

string1

The string to search in. Can be Aught.

string2

The string to find. This is the text you want to notice inside string1. It is normally shorter than string1. Tin can be Goose egg.

compare

Optional. Specifies the blazon of cord comparison.

vbBinaryCompare (0) performs a fast binary comparison where each character matches only itself. This is the default.
vbTextCompare (1) performs a text comparison.
vbDatabaseCompare (2). For Microsoft Access (Windows only), performs a comparison based on information contained in your database.
If you omit compare, InStr will use the setting of the Option Compare statement.

Binary comparison is the safest option. Text comparison rules vary by the system locale. In text comparison, what matches on one figurer may not match on another.

Return value

InStr returns 1 of the following values:

Null if either string1 or string2 is Zero.
first if string2 is empty.
Grapheme position of the start occurrence of string2 in string1.
Zilch if string2 was not found in string1.

Special cases:

InStr(string1, "") returns 1 if string1 is not empty.
InStr("", "") returns 0.
If beginning exceeds the length of string1, there is never a friction match and InStr returns 0.

Crash warning. The Nada return value is a grab. If your code is not prepared to receive a Zippo, it may crash. You can but receive a Null if y'all are passing Variant data to InStr and if the data tin can contain a Nix. If you are only passing String data, you are safe.

Basic uses of InStr

In the post-obit examples we assume that Pick Compare Binary is in consequence. This is the default setting.

Locate text within a string

                      ' Returns two            position = InStr("avbprogram", "vb")            ' Also returns 2            position = InStr("avbprogramvb", "vb")

Locate the first character in a string

                      ' Returns 2            position = InStr("avbprogram", "5")

Search in the rest of the cord

The start parameter is useful to skip the start of the string. This allows u.s.a. to skip known instances of the search cord.

                      ' Returns 11            position = InStr(4, "avbprogramvb", "vb")

It is also useful for optimizing calls to InStr. If we can beginning in the middle, the search volition perform faster.

Test for being of a substring

InStr is a quick and handy manner to exam whether a cord contains some other string. When the return value is positive, the string exists. When it is zilch, it doesn't exist. The render value cannot be negative. The operator <> is the fastest manner to decide between aught and positive.

          exists = InStr("avbprogram", "vb") <> 0

Test for not-being of a substring

If we desire to brand certain certain text does not exist within another string, we only reverse the existence test.

          notexists = InStr("avbprogram", "vb") = 0

Text comparison: Beware!

Bug alarm. The vbTextCompare parameter and Selection Compare Text are full of surprises. Text comparison performs a case-insensitive search, but it also adds new effects which aren't quite visible. This is a potential source of hidden bugs.

Did you know that InStr(one, "Straße", "ss", vbTextCompare) locates "ß" at position v? You searched for 2 characters, just InStr found simply one! What is this? The character "ß" stands for "ss" in the German language. Merely, if your program was expecting the two characters "ss" or "SS", it can fail now.

Similarly, InStr(1, "Þingvellir", "th", vbTextCompare) locates "Þ". This happens because the character "Þ" stands for "TH" in Icelandic. To InStr, the input reads "THingvellir". Once more, y'all searched for ii characters, but InStr institute just i.

How text comparison works

InStr with text comparison works with the following rules:

Perform a instance insensitive search
with the post-obit characters expanded:
ß→ss, þ→thursday, æ→ae, œ→oe.

Due to rule 2, the post-obit characters are dangerous: a, e, h, o, s, t. They are unsafe if whatsoever of the following characters tin be expected in the input: ßÞþÆæŒœ. One can await them in systems processing (foreign) names and addresses, for example.

Every bit it happens, the following expressions are not equal, even though they could exist. This means InStr volition not locate them in place of the other:

ø, oe and ö
æ and ä
å, aa and o
ð, d, dh and th
e and é
ĳ and ij (Dutch ligature vs. carve up characters)
i and İ (Turkish dotted İ)
ı and I (Turkish dotless I)
© and (c)
² and two
½ and 1/2
... and many more.

Instance insensitivity extends itself to a total of 673 Unicode characters. This includes all Latin characters and Latin characters with diacritic marks. It as well includes Greek, Cyrillic, Armenian and Georgian characters and Unicode Roman numbers.

Text comparison examples

InStr(1, "Ætna", "et", vbTextCompare) returns i. Notation how the lucifer happens "in the middle" of the AE ligature.
InStr(1, "Fjærland", "ja", vbTextCompare) returns 2. Note how the match happens for the "get-go half" of the ae ligature.
InStr(ane, "iß", "is", vbTextCompare) returns i, while InStr(1,"is","iß",vbTextCompare) returns 0.
InStr(1, "fœtus", "fetus", vbTextCompare) returns 0, while InStr(ane, "fœtus", "foe", vbTextCompare) returns i.
InStr(1, "Þingvellir", "howdy", vbTextCompare) returns 1.

Text comparison likewise affects other functions: InStrB, InStrRev, Supervene upon, Separate and StrComp. The effects are not exactly the aforementioned across these functions, so be conscientious. To name an case, in function Supervene upon "Þ" matches "th", but not "t" or "h" alone as with InStr.

Conclusion: vbTextCompare is not an ignore case flag. Avoid text comparison with InStr unless it really is what you want.

Case insensitive search: The safe way

At times we need to search in a case insensitive way. As explained above, there is no safe ignore case flag for InStr. The safest fashion to call "case insensitive InStr" is to convert both input strings into lower instance (or upper example).

                      ' Returns 2            position = InStr(LCase$("aVbprogram"), LCase$("VB"))

Optimization: If 1 of the strings is already in lower or upper case, information technology is plenty to catechumen the other.

                      ' Returns 2            position = InStr("avbprogram", LCase$("VB"))

Further optimization: If you are going to call InStr several times with the aforementioned parameter, practise your LCase$ first, store the outcome in a temporary variable and reuse the variable on each call. This mode you salve the repeated irksome telephone call to LCase$

Advanced uses for InStr

That was all most the basic uses of InStr. Let's get to know what else we can do with it.

Examination if grapheme belongs to ready

To see whether a character is one of given alternatives, call the following code:

          result = InStr("ABC", "A") <> 0

This test tells us if "A" is i of "A", "B" or "C".

Exam if string belongs to prepare

To encounter whether a longer piece of text is 1 of given alternatives, call the post-obit kind of code:

          event = InStr("-AB-CD-EF-", "-AB-") <> 0

This test tells us if "AB" is i of "AB", "CD" or "EF". Notation the utilize of separators, "-" in this example.

Locate `n`th occurrence of a string

If yous desire to find the due northth occurrence of a cord, non merely the first 1, set northward to the desired number and run the following code:

          position = 0 Do     position = InStr(position + ane, "avbprogramvb", "vb")     n = due north - one Loop Until position = 0 Or due north = 0

The loop keeps searching until the northth occurrence is found or there was no match. position tells the result.

To search for the northth occurrence after a certain beginning position, change the kickoff line to position = start - 1.

Search with additional examination

To acquit a search with additional conditions, run the following loop:

          position = 0 Do     position = InStr(position + 1, "avbprogram vb", "vb")     If position = 0 Then Exit Practice Loop Until            test(...) = Truthful

Each loop iteration performs a examination on each finding. The loop exits when the test condition is met or when there are no (more) matches. The test will typically examine the found position to see whether it contains an acceptable value or non. You tin can apply more complex tests such every bit the Similar operator, tests that you cannot incorporate in the InStr telephone call. In the above instance, the test could determine whether "vb" is a word or a function of a longer word.

Optimizing calls to InStr

What comes to pure functioning, InStr(,,,vbBinaryCompare) is a quick function. Its counterpart InStr(,,,vbTextCompare) is very slow. It actually pays off to utilize vbBinaryCompare with InStr.

You can use the start parameter to optimize InStr. Use start to skip the beginning of the string when y'all know the match must exist somewhere farther in the cord.

For InStr performance details, please read Optimize string handling in VB6, Part Ii.

InStrRev – Reverse search

InStrRev searches the string in opposite order: from the end to the start. It locates the concluding occurrence of a cord inside another.

          InStrRev(string1,            string2            [,            start] [,            compare])

Note how the parameters are in a different club than with InStr. Also note that InStrRev does not contrary the input strings: "ABC" does not lucifer "CBA".

InStrRev runs slowly, especially on a long string1. Utilise InStr when y'all tin can. More than

InStrB – Byte search

InStrB treats the input and search strings as byte arrays, non equally regular strings. Every bit you may know, VB uses Unicode strings. InStrB lets y'all by-laissez passer Unicode functionality and use byte arrays as if they were strings.

          InStrB([start,]            string1,            string2            [,            compare])

Instead of character position, InStrB returns the byte position of string2 in string1. InStrB should not exist used for regular string processing. InStrB is relatively fast and information technology tin can exist used for optimization, but it'due south tricky. Optimization with InStrB

Visual Bones InStr
URN:NBN:fi-fe20071012