Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: Searching with OR instead of AND criteria

    I currently have 4 text boxes that act as search criteria for sorting the table 'Region6'. The most important textbox is the one named 'Description', but there is a problem. Let's say I type in "yellow ruler". The search will return "yellow ruler 5" and "23b yellow ruler". However, it will not return "yellow broken ruler". In other words, it is almost like going to Google and searching for everything with quotes around it (exact match). Is there a way to make it so that typing in "yellow ruler" will return "yellow broken ruler"?

    Here is my code:

    Option Compare Database
    Option Explicit

    Dim strWhere As String
    Const csSql As String = "SELECT * FROM Region6 "
    Dim strOrder As String
    ------------------------------------------------------------
    Private Sub cmdSearch_Click()

    strWhere = "WHERE "

    strOrder = "ORDER BY Region6.Description;"

    If Not IsNull(Me.txtDescription) Then
    strWhere = strWhere & " (Region6.Description) Like '*" & Me.txtDescription & "*' AND"
    End If

    'Remove the last AND from the SQL statment
    strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

    'Pass the SQL to the RowSource of the listbox

    Me.lstCustInfo.RowSource = csSql & strWhere & strOrder

    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    check out the split() function

    after that, get your WHERE into shape with a series of Like AND Like AND Like from the output of split()

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2004
    Posts
    5

    Like this?

    You mean something like this?

    Option Compare Database
    Option Explicit

    Dim strWhere As String
    Const csSql As String = "SELECT * FROM Region6 "
    Dim strOrder As String
    Dim strArray() As String
    Dim intCount As Integer
    Dim strWhere2 As String
    ------------------------------------------------------------------------
    Private Sub cmdSearch_Click()

    strWhere = "WHERE "

    strOrder = "ORDER BY Region6.Description;"

    If Not IsNull(Me.txtDescription) Then
    If InStr(Me.txtDescription, " ") > 0 Then ' at least 2 words typed in.

    strArray = Split(Me.txtDescription, " ")

    For intCount = LBound(strArray) To UBound(strArray)
    strWhere2 = strWhere2 & "Region6.Description Like '*" & strArray(intCount) & "*' OR "
    Next
    strWhere2 = Trim(strWhere2) ' remove extra space out.
    strWhere2 = Left(strWhere2, Len(strWhere2) - 2) ' remove last OR out.
    strWhere2 = Trim(strWhere2) ' remove extra space out.
    strWhere = strWhere & " (" & strWhere2 & " ) AND" ' add ( ) and AND in / concatenate With strWhere.
    Else
    strWhere = strWhere & " (Region6.Description) Like '*" & Me.txtDescription & "*' AND"
    End If
    End If

    'Remove the last AND from the SQL statment
    strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

    'Pass the SQL to the RowSource of the listbox

    Me.lstCustInfo.RowSource = csSql & strWhere & " " & strOrder

    Debug.Print csSql & strWhere & strOrder

    End Sub

  4. #4
    Join Date
    Jul 2004
    Posts
    5
    The problem with the code I just posted is that whenever I try to search with anything in the "Description" box, the table simply clears and has 0 entries. I'm not very familiar with the Split(). Do you have any suggestions regarding how to fix my code?

    Thanks.

  5. #5
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Perhaps this may hopefully give you some ideas:

    Classic ASP Design Tips - Search For Keywords on Multiple Fields
    http://www.bullschmidt.com/devtip-se...iplefields.asp
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  6. #6
    Join Date
    Jul 2004
    Posts
    5
    Thanks for the link BullSchmidt. However, I'm a little confused about how to apply that SQL code to access. Could you provide a walkthrough to this (I'm kinda new to SQL)?

    Thanks.

Posting Permissions

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