One Text Box to filter Multiple Fields?

[manix]

HI all,

I have a relatively straightforward filter working on a search form where a string is built based on the criteria entered in the form. Easy peasey while you have one Txt or Cmb box on the form each filtering through one filed in the records, but what if I want one txt box to filer through 4 fields in my table?

Here is the code I use to build the string. I want txtcustomer to look at 4 fileds on the table and return all the records that have the criteria entered in any of the 4 fileds. This code works OK looking at [customer 1] only, but I want it to look at [customer 1], [customer 2], [customer 3] & [customer 4], which are all fields in my table.

If Not IsNull(Me.txtcustomer) Then
strWhere = strWhere & "([Customer 1] Like ""*" & Me.txtcustomer & "*"") AND "
End If

I have tried loads of different varients but none seem to work, can anybody help?

 

[manix]

OK figured out (well someone else did, thanks Chergh) how to get it to look at all cutomer fields. Now I am having problems with the syntax!

I get run time error 3075.

I have tried all of the following varients:

If Not IsNull(Me.txtcustomer) Then
for i = 1 to 4
strWhere = strWhere & "([Customer " & i & "] Like ""*" & Me.txtcustomer & "*"") OR "
next i
End If

As above but with:

strWhere = strWhere & "([Customer " & i & "] Like '*'" & Me.txtcustomer & "'*') OR "

as above but with:

strWhere = strWhere & "([Customer " & i & "] Like '*" & Me.txtcustomer & "*') OR "

It still keeps coming back with the 3075 code!!!!! Help, I am terrible at figuring out these formatting options! Can anyone nail why I am getting the problem?

 

[chergh]

try

If Not IsNull(Me.txtcustomer) Then
strWhere = strWhere & "("
for i = 1 to 4
strWhere = strWhere & "([Customer " & i & "] Like ""*" & Me.txtcustomer & "*"") OR "
next i
strWhere = strWhere & ")"
End If

Also are you selecting values from more than one table in your SQL statement?

 

[manix]

Ok, with help from Chergh, I have cracked the code required:

If Not IsNull(Me.txtcustomer) Then
    strWhere = strWhere & "("
    For i = 1 To 4
    strWhere = strWhere & "([Customer " & i & "] Like '*" & Me.txtcustomer & "*') OR "
    Next i
    End If
   
   
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
   If Not IsNull(Me.txtcustomer) Then
   lngLen = Len(strWhere) - 3
   
   If lngLen <= 0 Then 'Nah: there was nothing in the string.
      MsgBox "No criteria", vbInformation, "Nothing to do."
   Else 'Yep: there is something
      strWhere = Left(strWhere, lngLen)
      strWhere = strWhere & ")"
   End If

Else

   lngLen = Len(strWhere) - 4

   If lngLen <= 0 Then 'Nah: there was nothing in the string.
      MsgBox "No criteria", vbInformation, "Nothing to do."
   Else 'Yep: there is something
      strWhere = Left(strWhere, lngLen)
   End If
End If

        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End Sub