Search Bar Using Array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2002. I want to create a search bar in my form using a text
box. I plan to split out each word the user enters in the text box and then
build a query that filters these words.

I'm getting a "Type Mismatch" error at the moment, and I'm unsure why.

Here's the code:
Dim varSearchArray As Variant
Dim strWhereSQL As String
Dim itm As Variant

'split out the text entered in the search bar
varSearchArray = Split(DescSearch, " ")
'add each word to the where clause of the query
For Each itm In varSearchArray
strWhereSQL = strWhereSQL & " OR (((tlkpMediaCode.Desc)='" &
varSearchArray(itm) & "'))"
Next
strSearchSQL = Right(strWhereSQL, Len(strWhereSQL) - 4) & ";"
 
When you use For Each... on an array, it returns the actual data, not an
index, so you need to append:
itm
instead of
varSearchArray(itm)

It's whinging because itm contains text which is not valid as a numeric
index into an array.

BTW, instead of:
Right(strWhereSQL, Len(strWhereSQL) - 4)
you can say:
Mid(strWhereSQL, 5)
 
Back
Top