Like operator

[sillylady]

Hi All,

I have a table which has several fields and one field is 'Keywords' which hold values to search records, for example, if one record has Keywords such as (Bank, Report, NBFI) and 2nd as (Bank, NBFI, Credit). I am using Access (Like operator) and ASP to fetch data on user typed keywords. So if user input 'Bank' as search keyword. It displays both records but if a user input (Bank, NBFI) it only fetches 2nd record. It doesn't show both records.

Can any one help me where am I wrong?

Thanks

[vb_dba]

When you perform your comparison, you are searching for Like "*Bank, NBFI*". You would need to be able to ammend your query to add a Like clause for each keyword:

Dim sSql
Dim arrKeyWord
Dim i
 
arrKeyWord = Split(sKeyWordString, ",")
 
sSql = "Select * From myTable Where "
For i = LBound(arrKeyWord) To UBound(arrKeyWord)
    sSql = sSql & "keywordfield Like '*" & arrKeyWord(i) & "*' AND "
Next
'Remove the trailing AND
sSql = Left(sSql, Len(sSql) - 5)

Or something to that effect.

[sillylady]

Hi thanks for your solution. It works with a little bit of changes.

Thanks

When you perform your comparison, you are searching for Like "*Bank, NBFI*". You would need to be able to ammend your query to add a Like clause for each keyword:

Dim sSql
Dim arrKeyWord
Dim i
 
arrKeyWord = Split(sKeyWordString, ",")
 
sSql = "Select * From myTable Where "
For i = LBound(arrKeyWord) To UBound(arrKeyWord)
    sSql = sSql & "keywordfield Like '*" & arrKeyWord(i) & "*' AND "
Next
'Remove the trailing AND
sSql = Left(sSql, Len(sSql) - 5)

Or something to that effect.