Multiple search criteria on one field

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

Guest

Hello,

I have Access 2003. I have a table named tblTicket with the following fields.
Date
To
From
Problem
Solution

Let's say on some of my records, the data in the "Problem" field contains
the following:
1 My monitor's displayed is blurry and I can't see anything.
2 Today when I arrived, my monitor wouldn't turn on so I think it's broken.
3 I can't see anything today because it's blurry.
4 Things look blurry on my monitor/computer.

I need to create a way for my user to search the "Problem" field for
something like *monitor* and blur* and search results would find the
following:
1 My monitor's displayed is blurry and I can't see anything.
4 Things look blurry on my monitor/computer.

Can anyone point me in the right direction.

Thanks!
 
Hello,

I have Access 2003. I have a table named tblTicket with the following fields.
Date
To
From
Problem
Solution

Let's say on some of my records, the data in the "Problem" field contains
the following:
1 My monitor's displayed is blurry and I can't see anything.
2 Today when I arrived, my monitor wouldn't turn on so I think it's broken.
3 I can't see anything today because it's blurry.
4 Things look blurry on my monitor/computer.

I need to create a way for my user to search the "Problem" field for
something like *monitor* and blur* and search results would find the
following:
1 My monitor's displayed is blurry and I can't see anything.
4 Things look blurry on my monitor/computer.

Can anyone point me in the right direction.

Thanks!
Click to expand...

One method to allow this type of behavior is to build your query's
WHERE clause on the fly. It is a bit of a pain, because you first
must break the words in the text box into an array, then you can loop
through the array to build the conditions.

Example code might look like:

Dim arrSearchInput As Variant ' This variant will contain an array
of search input
Dim strSQLWhere As String ' This variable holds the WHERE
conditions

' Only build the WHERE clause if the search input has value
If Len(Trim(Me.Text0.Value)) > 0 Then

arrSearchInput = Split(Me.Text0.Value, " ")
strSQLWhere = " WHERE 1 = 1" ' This ensures a valid WHERE
clause
For i = 0 To UBound(arrSearchInput)
strSQLWhere = strSQLWhere & " AND [Problem] like '*" &
arrSearchInput(i) & "*'" ' Add a condition for each word in the text
box
Next i

End If

You can then append that WHERE clause to your query. Without knowing
more about your application, the suggested solution I give here might
not be appropriate.

- GH
 
Back
Top