Page 1 of 5 12345 LastLast
Results 1 to 15 of 73
  1. #1
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25

    A TRUE Keyword Search

    Like "*" & [Forms]![YourForm]![YourControl] & "*"

    The above query criterion is useful, but it does not create a true keyword search. Say I want a search in a single text box for "organic solutions" to bring up a record "Solutions Manual for Organic Chemistry." The above criterion will not work because the phrase "organic solutions" does not appear in the record field. But those words do.

    Does anyone know how to create a true keyword search so that my example would work? I'd prefer it to be a query criterion if possible, but I understand if it can't be done that way. Thanks!

  2. #2
    Guus2005's Avatar
    Guus2005 is offline Carbon based thingy
    Windows 7 Access 2003
    Join Date
    Jan 2010
    Location
    the netherlands
    Posts
    42
    You might want to try SoundEx search the forum for it, lot's of samples.

    SoundEx is a function which gives a number to each letter in a word to see if it matches another word which sounds similar.

    If that doesn't work, you need to add some code to split up the input from [Forms]![YourForm]![YourControl] and create separate Like clauses separated by the AND keyword.

    HTH

  3. #3
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    Thank you, Guus. I'm having trouble getting SoundEx to work, but formatting the text box the way you describe is exactly what I want to do. However, I don't know how to do it. Could you help or refer me to instructions somewhere?

    I simply need the text box to treat the value "blah blah blah" as a query for *blah* AND *blah* AND *blah*

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Meep,

    I have an example using Allen Browne's soundex function, but I'm not sure that will do what you want. How many of these "exact" keywords would you be using? 10, 100's??

  5. #5
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    If you mean how many unique words exist in the field I'm querying, then probably around 200-300. Although I wouldn't search for more than 7 at a time.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's an idea to consider. I am assuming the Keywords are in titles of articles or books or something. I'll work with Articles.

    Make a table called tblKeywords to contain your masterList of Keywords (you can always add to it)

    KeywordId (Pk)
    Keyword text

    1 Solution or whatever
    2 Chemical
    3 Chemistry....

    Then create another Table TblArticleHasKeyword
    This will contain the primary key of the Article and the primary key of the keywords contained in the Article Title.

    Id
    ArticleId fk to tblArticle
    KeyWordId fk to tblKeywords

    You will need a procedure to be used one time, and for each new Article, that will
    parse the Title and create records in the tblArticleHasKeyword.

    On a form to search for specific "Words", you can search using a query joining tblKeywords and tblArticleHasKeyword to look for the Keyword and return the Article(s) involved.

    If you are searching for multiple words, you would have multiple AND criteria.

    At best this would return 1 article, and at worst it would give you a list of Articles containing those Keywords in their Titles.

    Just some thoughts.


    I can still pass you the Soundex samples if you'd like.

  7. #7
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    If I were searching for multiple words, where would these AND criteria go? In the text box itself? I'm trying to avoid that.

    Also parsing each new record might be hard because I'm querying ODBC tables.

    I may be misunderstanding you, but nevertheless I would really appreciate the SoundEx samples. Thank you for the help.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is the Soundex code from Allen Browne's site with some of my info
    '---------------------------------------------------------------------------------------
    ' Procedure : Soundex
    ' Author : Allen Browne
    ' Date : 04-10-2011
    ' Purpose : Fuzzy search Soundex search
    '---------------------------------------------------------------------------------------
    ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency: N/A
    '--------------------------------------------------------------------------
    '
    Public Function Soundex(varText As Variant) As Variant
    On Error GoTo Err_Handler
    'Purpose: Return Soundex value for the text passed in.
    'Return: Soundex code, or Null for Error, Null or zero-length string.
    'Argument: The value to generate the Soundex for.
    'Author: Allen Browne (allen@allenbrowne.com), November 2007.
    'Algorithm: Based on http://en.wikipedia.org/wiki/Soundex
    Dim strSource As String 'varText as a string.
    Dim strOut As String 'Output string to build up.
    Dim strValue As String 'Value for current character.
    Dim strPriorValue As String 'Value for previous character.
    Dim lngPos As Long 'Position in source string

    'Do not process Error, Null, or zero-length strings.
    If Not IsError(varText) Then
    strSource = Trim$(Nz(varText, vbNullString))
    If strSource <> vbNullString Then
    'Retain the initial character, and process from 2nd.
    strOut = Left$(strSource, 1&)
    strPriorValue = SoundexValue(strOut)
    lngPos = 2&

    'Examine a character at a time, until we output 4 characters.
    Do
    strValue = SoundexValue(Mid$(strSource, lngPos, 1&))
    'Omit repeating values (except the zero for padding.)
    If ((strValue <> strPriorValue) And (strValue <> vbNullString)) Or (strValue = "0") Then
    strOut = strOut & strValue
    strPriorValue = strValue
    End If
    lngPos = lngPos + 1&
    Loop Until Len(strOut) >= 4&
    End If
    End If

    'Return the output string, or Null if nothing generated.
    If strOut <> vbNullString Then
    Soundex = strOut
    Else
    Soundex = Null
    End If

    Exit_Handler:
    Exit Function

    Err_Handler:
    MsgBox "Error " & Err.number & ": " & Err.Description, vbExclamation, "Soundex()"
    'Call LogError(Err.Number, Err.Description, conMod & ".Soundex")
    Resume Exit_Handler
    End Function



    Private Function SoundexValue(strChar As String) As String
    Select Case strChar
    Case "B", "F", "P", "V"
    SoundexValue = "1"
    Case "C", "G", "J", "K", "Q", "S", "X", "Z"
    SoundexValue = "2"
    Case "D", "T"
    SoundexValue = "3"
    Case "L"
    SoundexValue = "4"
    Case "M", "N"
    SoundexValue = "5"
    Case "R"
    SoundexValue = "6"
    Case vbNullString
    'Pad trailing zeros if no more characters.
    SoundexValue = "0"
    Case Else
    'Return nothing for "A", "E", "H", "I", "O", "U", "W", "Y", non-alpha.
    End Select
    End Function
    I have a table called OwnerAnimals and added a field (soundx)to contain the soundex value.

    I used this query to populate the soundx field in the records in the OwnerAnimals table
    UPDATE OwnerAnimals
    SET OwnerAnimals.Soundx = Soundex([AnimalName]);
    I used this query to find those animals with names = Soundex("Gar")

    SELECT OwnerAnimals.*
    FROM OwnerAnimals
    WHERE (((OwnerAnimals.Soundx)=Soundex("Gar")));
    Ihave attached jpgs showing the OwnerAnimals Table and the result of the Search query

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Meep, Did you try the code?

  10. #10
    dreday's Avatar
    dreday is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    58
    You could also use instr() to see if the key word is in the field

    Something like this:

    Code:
    dim keywords(1 to however many keywords allowed) as string
    dim match as boolean
    dim numofmatches as integer
    dim i as integer
    
    'keywords(0) = "Keyword 1"
    'keywords(1) = "Keyword 2"
    'and so forth....
    
    numofmatches = 0
    
    for i = 0 to ubound(keywords)
    
    if (instr("whatever the string you are searching is",keywords(i))<>0) then
    numofmatches = numofmatches + 1
    end if
    
    next i
    That should search all the keywords in the "keywords" array and compare them up against the string you are searching. Whatever the value of "numofmatches" after the loop completes is the total number of keywords that matched.

  11. #11
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    Quote Originally Posted by orange View Post
    Meep, Did you try the code?
    Well, I got the code to function, but the results aren't right. For example, a search for soundex("organic solutions") brings up only the title "optics" when there are at least 30 records with the words "organic" and "solutions" in them.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As I mentioned earlier you may have to parse your data to be searched into individual words, then store the recordid and keyword in a table.
    Then for search, compare the individual words to your keyword table.

    I don't think soundex will help with multiple words as you have shown.

    What exactly did you do?
    Well, I got the code to function
    Can you describe what these "records" represent?
    there are at least 30 records with the words "organic" and "solutions" in them
    Even some sample data might help.

  13. #13
    Guus2005's Avatar
    Guus2005 is offline Carbon based thingy
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    the netherlands
    Posts
    42
    to build your where clause form your input &quot;organic solutions&quot; you can try the following code.
    Code:
     Public function BuildWhereClause (strInput as string, strField as string) as string    dim arr() as string   dim intX as integer   dim strWhere as string   arr = split(strInput,&quot; &quot;)          for intX =0 to ubound(arr,1)         strwhere = strwhere & &quot; AND &quot; & strfield & &quot; like '*&quot; & arr(intx) & &quot;*'&quot;     next intX      BuildWhereClause = &quot;Where &quot; & mid$(strwhere,5)  end function
    Each input separated by a space is used to build your where clause. Example:
    Code:
         dim strSql as string     dim strInput as string      strinput = inputbox(&quot;Please enter where clause items&quot;)      strsql = &quot;select * from Table1 &quot; & BuildWhereClause(strInput, &quot;SearchField&quot;)  etc
    Enjoy!
    Last edited by Guus2005; 12-18-2011 at 11:32 AM. Reason: forum editter screws up the formatting!

  14. #14
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    Quote Originally Posted by Guus2005 View Post
    to build your where clause form your input &quot;organic solutions&quot; you can try the following code.
    Code:
     Public function BuildWhereClause (strInput as string, strField as string) as string    dim arr() as string   dim intX as integer   dim strWhere as string   arr = split(strInput,&quot; &quot;)          for intX =0 to ubound(arr,1)         strwhere = strwhere & &quot; AND &quot; & strfield & &quot; like '*&quot; & arr(intx) & &quot;*'&quot;     next intX      BuildWhereClause = &quot;Where &quot; & mid$(strwhere,5)  end function
    Each input separated by a space is used to build your where clause. Example:
    Code:
         dim strSql as string     dim strInput as string      strinput = inputbox(&quot;Please enter where clause items&quot;)      strsql = &quot;select * from Table1 &quot; & BuildWhereClause(strInput, &quot;SearchField&quot;)  etc
    Enjoy!
    I'm a moron and know nothing about code and where clauses, so can you kindly walk me step by step through exactly what I'm supposed to do with these two pieces of information? Paste them into a new module? If so, could you properly format the line breaks? Also, I should change the text "Table1" and "SearchField" to my things. Anything else? Thank you for your patience.
    Last edited by Meep; 12-19-2011 at 02:22 PM.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of your database in .mdb format? I have 2003 and can not open accdb

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL Parameters keyword?
    By Buakaw in forum Queries
    Replies: 1
    Last Post: 08-04-2011, 06:53 PM
  2. Incorrect syntax near keyword “ORDER"
    By k9drh in forum Import/Export Data
    Replies: 1
    Last Post: 06-13-2011, 07:36 AM
  3. Replies: 7
    Last Post: 04-29-2011, 03:44 PM
  4. Making a keyword search
    By timmy in forum Forms
    Replies: 9
    Last Post: 03-14-2011, 02:57 AM
  5. Extracting data after a keyword
    By KerryA in forum Queries
    Replies: 1
    Last Post: 02-11-2010, 09:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums