Using same unbound text box to filter multiple fields

[NickDV]

Hello,

I am trying to use the same unbound text box on a form to be used to
filter multiple fields. But I can not get it to work. The form has an
unbound text box called txtSubject and I would like to use it to
filter records in both the [Subject] field as well as the [Issue]
field. Actually I would like to use it on more fields, but this will
do for me to get the idea and to illustrate the problem.

Here is an example

'---------------------
'This ads the search for anything in the subject field
If Not IsNull(Me.txtSubject) Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Subject] Like ""*" & Me.txtSubject & "*"""
End If


'---------------------
'This ads the search for anything in the Issue field but not
working in combo with subject
If Not IsNull(Me.txtSubject) Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Issue] Like ""*" & Me.txtSubject & "*"""
End If

'-----------------------

Any sugestions on how to combine these two without having to create a
seperate text box for the [Issue] field would be appreciated.

Thanks

 

[NickDV]

Well after playing around with this, I came up with a solution that
works, but I'm not too crazy about it.
I created a hidden text box called txtIssue
Then using the after update event of the txtSubject box, I fill in the
hidden txtSubject

Me!txtIssue = Me!txtSubject

Then I modify the search code to include the new hidden text box:

If Not IsNull(Me.txtSubject) Then
sFilter = IIf(sFilter <> "", sFilter & " and ", sFilter)
sFilter = sFilter & "[Subject] Like ""*" & Me.txtSubject & "*"" Or"
sFilter = sFilter & "[Issue] Like ""*" & Me.txtIssue & "*"""
End If

If anyone knows a better way of doing this, please shed some light.
This will become kind of messy when I add the other 3 fields that I
want to search for.

Thanks

 

[NickDV]

I'm still having problems with this filter and would appreciate some
help.
The problem is that the filter does not work well when I add the OR
condition.
The idea is that I want to use the same text box to search in two
fields and show records that have it in either.
This does not work when I also want to satisfy other conditions such
as status.



'Filter Open
If Me.ChkClosed = 0 Then
strWhere = strWhere & "([Status] = 'Open') AND "
ElseIf Me.ChkClosed = -1 Then
strWhere = strWhere & "([Status] = 'Closed') AND "
End If
'********************************

'Filter Text

If Not IsNull(Me.txtSubject) Then
strWhere = strWhere & "([Subject] Like ""*" & Me.txtSubject &
"*"") Or "
strWhere = strWhere & "([Issue] Like ""*" & Me.txtIssue & "*"")
AND "
End If

'********************************