[AccessD] Paradigm for Keywords in a database

Eoin,

    Apparently, the most intricate scenario would be represented by a table having various key words scattered across various fields, and only those records are required to be displayed where one or more of the specified keywords appear across any of its fields.

    Sample query Q_Match, as given below, would get you the desired results. It makes use of function Fn_MatchKeyWord(), as given below. KeyString (the first argument required to be passed to this function) is a comma separated string of specified keywords. The order in which these keywords appear in the string, is immaterial. For current illustration, this string is "Brilliant,Access,Developer".

    In the sample query, T_Data is the name of table, while F1, F2, F3 are names of fields likely to hold any of the keywords. If there are more than three such fields, these can be concatenated suitably in the manner demonstrated.

Best wishes,
A.D.Tejpal
---------------

Q_Match 
(Sample Query for showing records
 having any of the matching keywords)
=================================
SELECT T_Data.* FROM T_Data 
WHERE Fn_MatchKeyWord("Brilliant,Access,Developer", [F1] & [F2] & [F3]) > 0;

=================================

Function Fn_MatchKeyWord()
=================================
Function Fn_MatchKeyWord( _
            ByVal KeyString As String, _
            ByVal FieldValue As String) As Long
    ' Returns 1 if there is a match with any
    ' of the kewords, otherwise 0
    ' KeyString is a comma separated string of
    ' keywords required to be matched
    Dim Match As Long, Cnt As Long
    Dim Rtv As Variant
    
    Match = 0       ' Default Value
    Rtv = Split(KeyString, ",")
    For Cnt = 0 To UBound(Rtv)
        If InStr(FieldValue, Rtv(Cnt)) > 0 Then
            Match = 1
            Exit For
        End If
    Next
    
    Fn_MatchKeyWord = Match
    
End Function
=================================

  ----- Original Message ----- 
  From: Eoin C. Bairéad 
  To: Access Developers discussion and problem solving 
  Sent: Tuesday, February 13, 2007 02:19
  Subject: [AccessD] Paradigm for Keywords in a database


  Hi

  I have a database where each record has a number of Keywords assocciated with it. I can't for the life of me see how to construct either  the table or a query to find, say, all records where the keywords "Access" Developers" and "brilliant" can be found.

  Any ideas ????

  Eoin