CODEWITHSUNDEEP
vbams-access
GettingStarted
GettingStarted

Reputation: 7625

My InStr is always returning 0

 englishReasonsToGoToSecondFloor = "test" & ";" & "exam & pay" & ";" & " possible fake"

So my values may contain a & or (space) or just a single word. Each item is separated by a ";"

so the final list will look like "test;exam & pay;possible fake" (the real list is much longer)

When the user selects an item from the Listbox, I want to quickly compare the selection with the words in the variable (if there is a better way to compare, please let me know)

rowValue = Trim(listboxTest.Column(1))
englishResult = InStr(1, rowValue, englishReasonsToGoToSecondFloor, CompareMethod.Text)

I can mouse over the rowValue and I see there is a value.

rowValue contains "possible fake"

but the englishResult is always 0

Upvotes: 0

Views: 947

Answers (3)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

it should be like below

englishResult = InStr(1, englishReasonsToGoToSecondFloor, rowValue, CompareMethod.Text)

EDIT

The Syntax for Instr function is

InStr( [Start], String1, String2, [Compare] )

where,

[Start] - An optional integer argument, representing the position that you want to start searching from. If omitted, the [Start] argument takes on the default value of 1.

String1 - The string that you want to search.

String2 - The substring that you want to search for.

[Compare] - An optional argument, specifying the type of comparison to make. This can be any of the following values:

vbBinaryCompare - performs a binary comparison

vbTextCompare - performs a text comparison

vbDatabaseCompare - performs a database comparison

If omitted, the [Compare] argument takes on the default value vbBinaryCompare.

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

englishResult = InStr(1, rowValue,"test", CompareMethod.Text) if englishResult=0 then englishResult = InStr(1,rowValue, "exam & pay", CompareMethod.Text) if englishResult=0 then englishResult = InStr(1, rowValue, " possible fake", rowValue,CompareMethod.Text)

Upvotes: -1

cyboashu
cyboashu

Reputation: 10433

not sure about Access.. but the Instr method works in Excel VBA with a master string before the search string so

InStr(1,englishReasonsToGoToSecondFloor, rowValue , CompareMethod.Text) this should work.

Upvotes: 2

Related Questions