I am trying to compare a value within an array with a text string. The array is generated by splitting a record containing a string. Please see the pseudo-code below.
Dim fish As Variant
fish = Split(myRS![Field2], " ")
If fish(Array reference hear?) = "whatever string" Then
'whatever else in hear
End If
I think I am having the following issues – referencing the correct part of the array and then converting the value to a string
If you need only to determine whether one or more of the array elements matches your search text, consider the Filter function.
In this example, Filter returns FishMatched as a one-dimensional array which contains the matching members from the first array (AllFish):
Dim AllFish As Variant
Dim FishMatched As Variant
AllFish = Split(myRS![Field2], " ")
FishMatched = Filter(AllFish, "whatever string")
If UBound(FishMatched) >= 0 Then
Debug.Print UBound(FishMatched) + 1; " match(es) found."
Else
Debug.Print "No match found."
End If
This approach may be suitable if you only need to know whether your search text is present among the members of AllFish. However, since it creates a new array containing the matches, it can not tell you the index of the AllFish member which matched.
You need a for loop to cycle the strings inside your array. LBound and UBound are needed to get the range of your array. You can check inside the loop if theStringYouAreLookingFor Is equal to the value of current array position i
Dim fish As Variant
Dim theStringYouAreLookingFor as String
theStringYouAreLookingFor = "here is what I'm looking for"
fish = Split(myRS![Field2], " ")
For i = LBound(fish) To UBound(fish)
If theStringYouAreLookingFor = fish(i) then
Debug.Print "Found it at " & cstr(i + 1) & " position in array"
Exit for
End If
Next i
