Multiple criteria in one field- query by form

[opekemi]

Hello all, I posted this in the wrong forum earlier.


I have a form that feeds a query with information, in my query I
currently have this:


[Company] Like [Forms]![QBF_Form]![Sales] & "*" Or
[Forms]![QBF_Form]![Sales] Is Null


When I fill in a value in one of the fields, e.g. "data*" the query
brings up all information with the word data it. If I don't fill

in any fields, the query returns all the values. So this is working
well so far.


However, when I fill in something like this: data* or vent* in one
of the
form fields, I don't get any query results, so each field works with
just one criteria.


How do I change the query expression above so I can search for multiple

criteria in one field using "or"?


Thanks a lot for your help!

 

[Allen Browne]

It is going to get messy and inefficient trying to do this in a query.

Could you create a form with an unbound text box in the form header where
the user enters their criteria, and you display the results (one per row) in
the continuous form?

If that sounds okay, you can use the AfterUpate event procedure of the text
box to parse the words, build the WHERE clause in code, and then assign it
to the Filter of the form.

The example below requires Access 2000 or later. It assumes the unbound box
is named txtKeywords, and the field you are matching is named Notes.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub