Searching multiple fields in a table

[KyleMac]

Edit: I now realize that's not my only problem with this setup. I'm trying to make it so the form will filter out any records that do not contain the filter set by item_in_a_textbox but the item could be in any of 10 fields.

I've got a table with I1-I10 as fields and I was attempting to run a search through the fields for a specific word. I'm not sure what I'm missing but I can't get it to recognize a string.

do while i < 11
     istr = "I" & i
     if [istr] = me.item_in_a_text_box then
         strFilter = strFilter & "([istr] = """ & Item & """)"
     end if
     i = i + 1
Loop

Edit40: I fixed it by just putting 10 filters in (with a do while loop):

do while i < 11
     istr = "I" & i
         strFilter = strFilter & "([" & istr & "] = """ & Item & """) OR "
     end if
     i = i + 1
Loop

[June7]

The string will have to include every field name with OR operators. The fields are all text type? What is the actual name of the textbox?

For i = 1 to 10
strFilter = "[I" & i & "]=""" & Me.textboxname & """ OR "
Next
strFilter = Left(strFilter, Len(strFilter) - 4)
Me.Filter = strFilter
Me.FilterOn = True


If only one of the 10 fields can have a value for each record, then this should be one field with 10 choices. If any or all of the 10 fields can have data, then really should be another related table.

[orange]

You may find the approach used in this search useful.