Trim statement with an array

[Guest]

Hello,
I have the following code which will look at the values typed into a text
box, determine whether or not there is more then one name, then create an
array if there is more than one name and put the results into a select
statement. It seemed to be working fine until I added the "Trim" line. Am I
not allowed to trim an array? The user may enter one name like "Harold Bloom"
or he may enter something like "Harold Bloom, Philip Roth". I need to remove
that space between the comma and "Philip Roth" so I used the Trim statement
but it keeps telling me the subscript is out of range. Thanks very much for
your help in advance.

If Len(Trim(vstrSearch)) > 0 Then
If vstrResult > 0 Then
straryElement = Split(vstrSearch, ",")
intFinalNumber = UBound(straryElement)
End If

For intCounter = 0 To intFinalNumber
straryElement(intCounter) = Trim(straryElement(intCounter))
If intFinalNumber > 0 Then
If intCounter = 0 Then
strSQLSearch = "'" & straryElement(intCounter)
Else
strSQLSearch = strSQLSearch & "' OR " & vTextHeading &
"='" & straryElement(intCounter) & "'"
End If
End If
Next intCounter

End If

 

[Brendan Reynolds]

Your array gets dimensioned only if the value of vstrResult is greater than
0, but subsequent code attempts to loop the array regardless of whether it
has been dimensioned or not ...

If Len(Trim(vstrSearch)) > 0 Then

'Array dimensioned only if value of vstrResult greater than 0

If vstrResult > 0 Then
straryElement = Split(vstrSearch, ",") 'array dimensioned only
if value of vstrResult greater than 0
intFinalNumber = UBound(straryElement)
End If

'Attempt to loop array that may not have been dimensioned.

For intCounter = 0 To intFinalNumber
straryElement(intCounter) = Trim(straryElement(intCounter))
If intFinalNumber > 0 Then
If intCounter = 0 Then
strSqlSearch = "'" & straryElement(intCounter)
Else
strSqlSearch = strSqlSearch & "' OR " & vTextHeading &
"='" & straryElement(intCounter) & "'"
End If
End If
Next intCounter
End If

 

[Guest]

Thank you so much for your reply. I'm afraid, though that I don't quite
understand what I'm supposed to do to fix the problem - put all the code so
that it falls within the first IF statement? I'm also not sure why it worked
until I added the trim statement. Could you possibly clarify?

 

[Brendan Reynolds]

The question you need to ask yourself is, what do you want to happen if the
value of vstrResult is *not* greater than zero? That's not a question that I
can answer for you, as I have no idea what that value represents or how it
relates to the problem at hand.