Microsoft Access VBA Macro Programming

The variablexwill have the value richard.

In both of these examples, any nonletter characters such as numbers will be left as they

are.

Searching Strings

Another function can search a string for a specified substring. This function is calledInstr,

which stands for “in string.” The syntax for this is fairly complicated because two of the

arguments are optional:

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

Startis an optional parameter and shows where in the string the search should start from. If

it is omitted, the search starts from position 1.Startmust not contain a null value, and if

startis used, then theCompareparameter must be used.

String1is the string being searched (for example, "Richard Shepherd").String2is the

string being sought (for example, "shepherd").

Compareis the technique used to compare the strings. The possible values are

vbBinaryCompareandvbTextCompare. In simple terms, this determines whether the

search is case sensitive or not. Binary compare uses the actual binary value, soAequalsA,

butAdoes not equala.Text compare ignores case, soAwill equala.A null value here will

produce an error. The default forCompareis binary, which means it is case-sensitive.

Table 5-1 lists the values theInstrfunction produces. Here is a simple example:

x=Instr("Richard Shepherd","Shepherd")

This will give an answer of 9.

Note that the default compare flag is binary/case sensitive:

x = Instr("Richard Shepherd","shepherd")

48 Microsoft Access 2010 VBA Macro Programming

Value Returned by Instr Meaning

0 String1is zero length.

Null String1is null.

Start Value String2is zero length.

Null String2is null.

0 String2 not found.

Position Position ofString2withinString1.

0 Start is greater than length ofString1.

Table 5-1 Values of the Instr Function