KK wrote:
hello everyone..
i want to search the data in my database word by word. I mean if i put
the something like this in the search textbox "age cellphone date" i
get the list of the every column n row containing the words "age
cellphone date"
is it possible to write such query, plz help me on this and tell me
what things are feasible and if this is not feasible wat are its
alternatives?
Thanks
K
If using something beyond A97, this will get you started. Copy this
code into a code module and run it to see the result.
I have a variable called strAndOr. If you passed 3 search words but you
want to see the row if any of the words match, use Or. If all words
must match, use And.
Sub Test()
Dim s() As String
Dim i As Integer
Dim strFilter As String
Dim strFieldName As String
Dim strAndOr As String
Dim intLen As Integer
Dim strSQL As String
'"And" where all words in field to be searched must match
'"Or" for one of the words must match
'Change to "And" or "Or" depending on type of search
strAndOr = "And"
'get the length or And/Or. Used to remove training word
intLen = Len(strAndOr) + 2
'the field name to search. Can be passed as argument
strFieldName = "Address"
'break out the words to search
s = Split("age cellphone date", " ")
'create the filter string
For i = 0 To UBound(s)
strFilter = strFilter & strFieldName & " Like *" & _
s(i) & "* " & strAndOr & " "
Next i
'Create SQL string
strSQL = "Select * From Customer "
'now remove the and/or at end of filter string
If strFilter "" Then strSQL = strSQL & "Where " & _
Left(strFilter, Len(strFilter) - intLen)
'display the SQL if you'd like
MsgBox strSQL
'process however
'...
End Sub
I C U
http://www.youtube.com/watch?v=zb9iDNLtTbE 
