Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Dec 2005
    Posts
    67

    Unanswered: Keyword Search Query

    Hi,

    I've tried building a search form which works up to a point. It works on 'or' queries, but if i want to do a search which requires an 'and' condition, it doesn't work. I'm thinking of starting from scratch, does anyone have any suggestions or samples please?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show your query, your table layout, and examples of keywords
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Posts
    67

    Keyword Search Query

    This is the bit for when you click on the search button.
    Code:
    Private Sub cmdSearch_Click()
    On Error GoTo Err_cmdSearch_Click
    
        Dim stDocName       As String
        Dim stLinkCriteria  As String
        Dim frm             As Form
        Dim lst             As ListBox
        Dim lbl             As Label
        Dim sColumns        As String
        
        sColumns = BuildSelectSQL
        SetKeywordsearchfilter
        If Len(sColumns) > 0 And Len(Keywordsearchfilter) > 0 Then 'If display options and criteria selected
            stDocName = "F_Results"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
            Set frm = Application.Forms(stDocName)
            Set lst = frm.Controls("lstResults")
            Set lbl = frm.Controls("lblCount")
            lst.ColumnCount = mlNumberOfCols
            msSQL = "SELECT " & sColumns & " FROM intermediaryPayments WHERE " & Keywordsearchfilter
            lst.RowSource = msSQL
            lbl.Caption = lst.ListCount - 1
        Else
            'Notify user if nothing selected
            MsgBox "Invalid search criteria.", vbCritical
        End If
    
    Exit_cmdSearch_Click:
        Exit Sub
    
    Err_cmdSearch_Click:
        MsgBox Err.Description
        Resume Exit_cmdSearch_Click
        
    End Sub
    This is the keyword search filter
    Code:
    'Field names to be searched.
    Private Sub SetKeywordsearchfilter()
        Keywordsearchfilter = ""
        'Test the following here to avoid being prompted everytime
        If InStr(1, Keyword1.Value, "OR", vbBinaryCompare) = 1 Or InStr(1, Keyword1.Value, "AND", vbBinaryCompare) = 1 Then
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("AbrufNo", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("ProjectName", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("ProjectCountry", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("IntermediaryName", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Trustee", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("BC_Country", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("AccountNameInstr", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("PaymentDate", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("PaymentYear", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("AccountNameOrder", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("ARE_BankCodeOrder", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Signatory1Instr", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Signatory2Instr", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Signatory3Instr", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Signatory4Instr", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Signatory1Order", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Signatory2Order", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Signatory3Order", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Signatory4Order", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Sign1CaseOfSuc", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Sign2CaseOfSuc", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Sign1LOA", "like", Keyword1))
            Keywordsearchfilter = AddClause(Keywordsearchfilter, "OR", MakeStringClauseForTextbox("Sign2LOA", "like", Keyword1))
        Else
            MsgBox "Please type 'AND ' or 'OR ' in the control: " & Keyword1.Name, vbCritical
        End If
    End Sub
    This is the make string clause for textbox bit:
    Code:
    Public Function MakeStringClauseForTextbox(sColumnName As String, sOperator As String, txt As TextBox) As String
        Dim sSQLWhere       As String
        Dim sFieldName      As String
        Dim sText           As String
        Dim lFoundComma1    As Long
        Dim lFoundComma2    As Long
        Dim lStart          As Long
        Dim sANDOR          As String
        Dim lDeleteANDOR    As Long
        Dim bValid          As Boolean
        
        sFieldName = "[" & sColumnName & "] "
    On Error Resume Next
        sText = Trim$(txt.Value)
    On Error GoTo 0
        If Len(sText) Then
            bValid = True
            If InStr(1, sText, "OR", vbBinaryCompare) = 1 Then
                sANDOR = " OR "
                sText = Trim$(Right$(sText, Len(sText) - 2))
                lDeleteANDOR = 4
            ElseIf InStr(1, sText, "AND", vbBinaryCompare) = 1 Then
                sANDOR = " AND "
                sText = Trim$(Right$(sText, Len(sText) - 3))
                lDeleteANDOR = 5
            Else
                bValid = False
            End If
            If bValid And Len(sText) > 0 Then
                sSQLWhere = " ("
                lFoundComma1 = 1
                lFoundComma2 = 1
                'lStart = 1
                While lFoundComma1 > 0 And lFoundComma2 > 0
                    lFoundComma1 = InStr(lStart + 1, sText, ",", vbTextCompare)
                    lFoundComma2 = InStr(lFoundComma1 + 1, sText, ",", vbTextCompare)
                    
                    If sOperator = "like" Then
                        If lFoundComma1 > 0 Then
                            sSQLWhere = sSQLWhere & sFieldName & sOperator & " '*" & Mid$(sText, lStart + 1, lFoundComma1 - (lStart + 1)) & "*' " & sANDOR
                        End If
                    Else
                        If lFoundComma1 > 0 Then
                            sSQLWhere = sSQLWhere & sFieldName & sOperator & " '" & Mid$(sText, lStart + 1, lFoundComma1 - (lStart + 1)) & "' " & sANDOR
                        End If
                    End If
                    
                    lStart = lFoundComma1
                Wend
                If Len(Mid$(sText, lFoundComma1 + 1)) > 0 Then  'Anything after last comma found
                    If sOperator = "like" Then
                        sSQLWhere = sSQLWhere & sFieldName & sOperator & " '*" & Mid$(sText, lFoundComma1 + 1) & "*' " & sANDOR
                    End If
                End If
                sSQLWhere = Left$(sSQLWhere, Len(sSQLWhere) - lDeleteANDOR)
                sSQLWhere = sSQLWhere & ") "
            Else
                If bValid Then
                    MsgBox "Please enter valid criteria in the control: " & txt.Name, vbCritical
                Else
                    MsgBox "Please type 'AND ' or 'OR ' in the control: " & txt.Name, vbCritical
                End If
            End If
        End If
        MakeStringClauseForTextbox = sSQLWhere
    End Function
    Examples of keywords:
    China
    Rainer
    Cyprus
    Pohland

    Table Structure:
    Just a flat table with field names some of which are in the above keywordsearch filter code sample above.

    Hope this helps. Thank you. (Sorry for the long post)

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perhaps this may be of some use to you.
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2005
    Posts
    67

    Keyword Search

    Wow - looks really good and like what I want. Will let you know how it went. Thanks a zillion.

  6. #6
    Join Date
    Dec 2005
    Posts
    67
    Does anyone have any tips on how to create a general keyword search? i.e. there will only be one textfield box that the user enters text into, but the search searches all the fields in the underlying table?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's pretty convoluted but it should work...
    Code:
    Dim SQL As String
    
    SQL = ""
    SQL = SQL & " SELECT * FROM TheTable WHERE"
    SQL = SQL & " Field1 = '" Me.txtSearchField.Value & "'"
    SQL = SQL & " OR Field2 = '" Me.txtSearchField.Value & "'"
    SQL = SQL & " OR Field3 = '" Me.txtSearchField.Value & "'"
    'Etc...
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    However, if we're using the like comparison, like so:
    Code:
    Dim SQL As String
    
    SQL = ""
    SQL = SQL & " SELECT * FROM TheTable WHERE"
    SQL = SQL & " Field1 LIKE '%" Me.txtSearchField.Value & "%'"
    SQL = SQL & " OR Field2 LIKE '%" Me.txtSearchField.Value & "%'"
    SQL = SQL & " OR Field3 LIKE '%" Me.txtSearchField.Value & "%'"
    Then I believe this is more efficient
    Code:
    Dim SQL As String
    
    SQL = ""
    SQL = SQL & " SELECT * FROM TheTable WHERE"
    SQL = SQL & " Field1 & Field2 & Field3 LIKE '%" Me.txtSearchField.Value & "'"
    Note: the highlighted characters may need swapping to plus signs for this to work in Access
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2005
    Posts
    67
    Thanks - yes it's going to run both AND and OR searches.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Both of the above are OR searches
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2005
    Posts
    67
    How would I adapt it for an AND search? Do you know?

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In the first query, you swap the word "OR" to "AND"
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Then I believe this is more efficient
    Code:
    Dim SQL As String
     
    SQL = ""
    SQL = SQL & " SELECT * FROM TheTable WHERE"
    SQL = SQL & " Field1 & Field2 & Field3 LIKE '%" Me.txtSearchField.Value & "'"
    Note: the highlighted characters may need swapping to plus signs for this to work in Access
    I'm not sure about that George. But it certainly could be more innaccurate. If field 1 is "a", 2 is "b" and 3 is "c" then searches for "abc" will return rows.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I did realise the possibility for innacuracies, perhaps we can separate each field with a space?
    Either way, I ran a handful of quick tests earlier and it was nearly 3x faster!
    If you're interested I can post the script I ran (on MSSQL2K) to show this.
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I did realise the possibility for innacuracies, perhaps we can separate each field with a space?
    Either way, I ran a handful of quick tests earlier and it was nearly 3x faster!
    If you're interested I can post the script I ran (on MSSQL2K) to show this.
    Please do.

    What happens if someone searches for "a b"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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