Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Question Unanswered: Search within a text field for non contiguous text.

    Hi All

    I have been struggling to to build a search query on a single text field where someone can type in to a search box a single word, a string of contiguous words or a number of non contiguous (key)words and have the results presented in a new form.

    The results would show matches where

    the single word appears anywhere in the field (currently this Like "*" & [Enter keyword or phrase to search for] & "*" works fine for this condition)

    AND

    the string of contiguous words appear anywhere in the field (currently this Like "*" & [Enter keyword or phrase to search for] & "*" works fine for this condition)

    AND

    any of the non contiguous words appear anywhere in the field. (This I can't figure out)

    Ideally it would be great if the search results would appear in this order

    1. string of contiguous words (perfect match) appears at the top.
    2. single word (perfect match) appears next
    3. the non contiguous word appearing anywhere in the field appear last

    and it would be even better (wishlist!) if the results would show words as highlighted (in say a different colour or other font attribute) that match the search words inputted.

    I'm thinking that in order for the non contiguous search option to work, Access would have to "break apart" the search phrase and then search the field for single word matches. But if this happens I wouldn't want the same record showing multiple times as a result of the search. Maybe I need to have the search build a table of results, then present that. But then my head starts to hurt

    Has anybody seen or attempted this before?

    I have searched widely but haven't been able to find a solution.

    Any assistance gratefully appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im pretty certain you will need to use vba to create the query.
    Decide whether SELECT DISTINCT or SELECT DISTINCTROW are apappropriate to eliminate duplicates.
    consider using a form to build your list of search terms. Having built the form then either save the resultant query in the querydefs collection or pull those parameters into the consuming report or form. Given the nature of what you want right now i think youd be better off useusing the querydefs collection
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Healdem

    I didn't think it was going to be easy. I'll take on board what you have suggested, but to be honest I don't have much of an idea about creating a query using VBA.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the basic problem, is capturing the values to be used in a way that can be inserted into a query.
    personally I dont' like using the method to get users to enter parameters at runtime( the [enter value here...]) as it means a user can inadvertantly or even maliciously enter bad data.
    in short I don't trust user input, I alwasy validate it as best as possible before running against data. that validation may be just to ensure its the right type or is a valid date or it may be a specific range,, or it mnay be a selection from a list/combo box
    So as a consequence I nearly always use forms (one to hold the current values, one to validate and so on_. Another advantage of using a form to validate input is that you can supply sane values when the form first loads

    But your main issue is the random nature of how many words your users are going to enter when searching. That and a truly fluid search form may mean a user may want to look at one element to search, whereas with your current appraioch you have to enter in values for all the requests...

    in essence you are writing a query. that wuery will take the form of

    SELECT DISTINCT(my, column,list) FROM mytable
    WHERE anumericcolumn =1234 AND astringcolumn = 'blah' AND adatecolumn = #2015/03/10#
    ORDER BY a, column, list

    the select bit identifies what columns you want to return from the table(s), the distinct means unique values of the columns identified in the brackets. as said earlier DISTINCTROW may be better for your purposes

    the ORDER BY bit identifies what the sort order should be, and is optional

    the WHERE bit is where you identify what 'filters' should be used to limit the rows returned.

    from what you have said so far I'd expect several controls on your form
    one would be a combo box, allowing the user to type in free form words (add 'em to the combo box

    when looking for workds in text you need to use wildcards. it depend on your setup as to whether you use the Ac cess/VBA wildcards symnbiols or ANSI/ISO SQL wildcards. I tend to use ISO SQL wildcards so code is portable between different dataabases (its also one less thing the feck up when writing code for MySQL, SQLite etc or Access/JET

    ..Personally I don't have time to do a full featured reply for you, Im quite happy to help out as and where I can but you will need to do the groundwork yourself


    But I'd suggest you use a form, palce wahtever controls you feel you need (cpuld be a combo, could be a text box and a list box. its yuour form, your design yoru call.

    get the user to add words to the combo or list box

    add a command button which then generates the query and (I think) save it to the querydefs collection and use that query in subsequent forms or reports

    lets say you want to search for the occurance of specified terms in a column called mycolumn
    as far as SQL is concerned it doesn't matter if 'specified terms' is a word, a (partial) sentence a fragment of a word and so on... you habdle that by what wildcard you use

    ..so for the follwoing regard term as a synomym for what you want to find
    if you want to find a specific word as a tem put a space before and after the term
    eg
    WHERE mycolumn like "% word %"

    if you wanted word list
    WHERE mycolumn like "% word list %" //this would return rows which had the separate TERM 'word list' (NOTE the spaces as padding that tells the SQL engine that only word followed by list is acceptable NOT 'sword list' or 'word listing'. if you wanted those then '%word list%' would do that.

    if you wanted words ending in word (eg word, sword, afterword....)
    WHERE mycolumn like "%word %" //note the leading space is removed


    if you wanted words starting with word (eg word, wordy....)
    WHERE mycolumn like "%word %" //note the leading space is removed

    if you want rows inclduing the term 'word'
    WHERE mycolumn like "%word%" //note the leading space is removed

    if you want to apply multiple terms to a the same column use the in style
    WHERE mycolumn in ('%word%', '%symbol%', '%MS Access%')
    Unless oemoine else can give you more time I'd suggest readign up on how to add values to a combo or list box, buil you list of temrs to search for
    then read up on how to create the query
    then how to use that query (either as part of the querydefs collection or directly). but Im nto going to be able to provide much more detail, I'll help where I can, but it will bemolre pointers than answers
    Last edited by healdem; 03-10-15 at 09:35.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Healdem

    Thanks for your excellent reply. I can see that there is a lot that I'm going to work on for this project. You have given me some great clues and at least an idea on where to start. I'll start beavering away!

  6. #6
    Join Date
    Mar 2015
    Posts
    27

    An easy solution using regex

    Create this code module:
    Code:
    Option Compare Database
    Option Explicit
    
    '** IMPORTANT!!! : Use of  this module requires a reference to
    '**     Microsoft VBScript Regular Expressions 5.5 or newer
    
    Private rgxSearchPhrase As String
    Private rgxSearchWords As String
    Private intTotalPhraseValue As Integer
    Private pRGX As RegExp
    
    Public Sub CreateRegExSearch(strSearchPhrase As Variant)
        Dim arWords() As String
        
        Set pRGX = New RegExp
        pRGX.ignorecase = True
        pRGX.Global = True
        
        rgxSearchPhrase = Nz(strSearchPhrase, "")
        rgxSearchPhrase = Replace(Replace(rgxSearchPhrase, "  ", " "), "  ", " ")
        If Len(rgxSearchPhrase) > 0 Then
            arWords = Split(rgxSearchPhrase, " ")
            rgxSearchWords = "\b" & Join(arWords, "\b|\b") & "\b"
        Else
            rgxSearchWords = ""
        End If
    
    End Sub
    
    Public Function GetMatchCount(TextToSearch As Variant) As Integer
        Dim mc As MatchCollection
        Dim FoundFullPhrase As Boolean
        
        If (pRGX Is Nothing) Or (Len(Nz(rgxSearchPhrase, "")) = 0) Then
            GetMatchCount = 0
            Exit Function
        End If
        
        pRGX.Pattern = rgxSearchPhrase
        Set mc = pRGX.Execute(TextToSearch)
        FoundFullPhrase = (mc.Count > 0)
        pRGX.Pattern = rgxSearchWords
        Set mc = pRGX.Execute(TextToSearch)
        GetMatchCount = mc.Count + IIf(FoundFullPhrase, 1, 0)
        
    End Function
    
    Public Function FullPhraseMatch(TextToSearch As Variant) As Boolean
        Dim mc As MatchCollection
        Dim FoundFullPhrase As Boolean
        
        If (pRGX Is Nothing) Or (Len(Nz(rgxSearchPhrase, "")) = 0) Then
            FullPhraseMatch = False
            Exit Function
        End If
        
        pRGX.Pattern = rgxSearchPhrase
        Set mc = pRGX.Execute(TextToSearch)
        FullPhraseMatch = (mc.Count > 0)
    
    End Function
    Create a query similar to this to return your search results:
    Code:
    SELECT ID, GetMatchCount([TextStrings]) AS Matches, FullPhraseMatch([TextStrings]) AS FullPhraseMatch,TextStrings
    FROM tblSampleTexts
    WHERE (((GetMatchCount([TextStrings]))>0))
    ORDER BY GetMatchCount([TextStrings]) DESC , FullPhraseMatch([TextStrings]) DESC;
    If you have a search phrase text box called SearchText and an embedded subform control called subfrmCtlMatchesList which contains a datasheet using a query like above, and a button called btnSearch on your search form to invoke the search, you would call the whole thing like this:

    Code:
    Private Sub btnSearch_Click()
        Call CreateRegExSearch(Me.SearchText)
        Me.subfrmCtlMatchesList.Requery
    End Sub
    The functions shown in the code module will handle null search phrases and null record fields appropriately.


    I have included a sample Access 2010 database with a table of sample strings to search against and a search form with embedded sub form to show the results. A check mark appears next to the records with exact matches, with a column with the total match count for that record next, and the text field searched next. The match count for a phrase 4 words long will have a match count of at least 5, one match for each of the 4 words in the search phrase, plus one match for the full phrase match. If there are words within the phrase that appear more than once in the text field searched, they will add to the match count. If the sort order in my example query is maintained, then the exact phrase matches will sort first with those having the highest matches count in descending order, followed by the non full phrase match results with match counts in descending order.
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hello Rodrich

    Thank you very much for your solution. I'll give it a go and let you know how I end up.

    Not sure what you mean by
    '** IMPORTANT!!! : Use of this module requires a reference to
    '** Microsoft VBScript Regular Expressions 5.5 or newer


    Is this something that needs to be installed on each PC that the DB is running on?

  8. #8
    Join Date
    Mar 2015
    Posts
    27

    References required....

    Quote Originally Posted by sheusz View Post
    Hello Rodrich

    Thank you very much for your solution. I'll give it a go and let you know how I end up.

    Not sure what you mean by
    '** IMPORTANT!!! : Use of this module requires a reference to
    '** Microsoft VBScript Regular Expressions 5.5 or newer


    Is this something that needs to be installed on each PC that the DB is running on?
    Actually it will already be there on most PCs I expect. In the VBA editor for Access, go to the Tools menu item, select References, then scroll through the list of items and find the one that matches the above. Check mark it and then click OK and save your database.

  9. #9
    Join Date
    Mar 2015
    Posts
    27

    Sort order

    I noticed that the sort order my example provides is not quite what you asked for as the ideal ordering when tested against my sample database. To get what you want, remove the sort order portion of the sql and add the line [qryMatchWords].[FullPhraseMatch], [qryMatchWords].[Matches] to the display form for your results instead and set Order On Load to yes. That SHOULD give you the results you were asking for. It does for me when I test it against the sample database. I will reupload the corrected sample.
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hello Rodrich1954

    Still working on your answer to this question. I've encountered a few errors, but am trying to sort them out myself before asking for more help.

    Thanks again for your assistance thus far.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •