Search boxes to with multiple criteria (1 Viewer)

[Db-why-not]

I have 2 textboxes txtkeywords and txtkeywords2 that I am want to use to do keyword searchs.I cant get the code to work.

Private Sub cmdsearch_Click()  
Dim SQL As String
    SQL = "SELECT q_Qual_Cond.[Chron#], q_Qual_Cond.[Research_ID], q_Qual_Cond.[Qualifying_Condition] " _
 & "FROM q_Qual_Cond " _  & "Where [Qualifying_Condition] Like '*" & Me.txtkeywords & "*' " _
 & "ORDER BY q_Qual_Cond.[Chron#], q_Qual_Cond.[Research_ID]; "    

Dim SEQL As String   
SEQL = "SELECT q_Qual_Cond.[Chron#], q_Qual_Cond.[Research_ID], q_Qual_Cond.[Qualifying_Condition] " _ 
 & "FROM q_Qual_Cond " _
 & "Where [Qualifying_Condition] Like '*" & Me.txtkeywords2 & "*' " _
 & "ORDER BY q_Qual_Cond.[Chron#], q_Qual_Cond.[Research_ID]; "

Me.sub_Qual_cond.Form.RecordSource = SQL Or SEQL
Me.sub_Qual_cond.Form.Requery

End Sub

What do I need to do to fix the code?
Or How can I use 1 textbox that would let me type in multiple keywords?
Thanks.

 

[isladogs]

Db-why-not
I've added code tags and line breaks to give members a chance to be able to read your post!

Three initial comments from me
1. Line endings should be " & _ NOT " _ &
2. The line ending in .RecordSource =SQL or SEQL is definitely wrong.
3. There should be a space before predicates such as FROM, WHERE, ORDER BY

Suggest you explain more clearly what you are trying to do.

 

[MajP]

I have a text box with
dog cat bird mouse
on the form

Private Sub cmdsearch_Click()
  Dim SQL As String
  Dim strWhere As String
  strWhere = GetMultiLike(Me.txtMulti, "qualifying_Condition")
   SQL = "SELECT q_Qual_Cond.[Chron#], q_Qual_Cond.[Research_ID], q_Qual_Cond.[Qualifying_Condition] " _
   & "FROM q_Qual_Cond " _
   & strWhere & " " _
   & "ORDER BY q_Qual_Cond.[Chron#], q_Qual_Cond.[Research_ID]; "
 Debug.Print SQL
End Sub


Public Function GetMultiLike(ctrl As Access.TextBox, fieldName As String, Optional Delimiter As String = " ") As String
  Dim aLike() As String
  Dim i As Integer
  aLike = Split(ctrl, Delimiter)
  
  For i = 0 To UBound(aLike)
    If GetMultiLike = "" Then
      GetMultiLike = fieldName & " Like '*" & aLike(i) & "*'"
    Else
      GetMultiLike = GetMultiLike & " OR " & fieldName & " Like '*" & aLike(i) & "*'"
    End If
  Next i
  If GetMultiLike <> "" Then
    GetMultiLike = "WHERE " & GetMultiLike
  End If
End Function

output

SELECT q_qual_cond.[chron#], 
       q_qual_cond.[research_id], 
       q_qual_cond.[qualifying_condition] 
FROM   q_qual_cond 
WHERE  qualifying_condition LIKE '*dog*' 
        OR qualifying_condition LIKE '*cat*' 
        OR qualifying_condition LIKE '*bird*' 
        OR qualifying_condition LIKE '*mouse*' 
ORDER  BY q_qual_cond.[chron#], 
          q_qual_cond.[research_id];

Are the keywords set or dynamic? If you have a set number of keywords then you can use a multiselect listbox and loop that. I have a class module for that.

 

[theDBguy]

Hi. What's the original SQL Record Source of the form? If it's the same as what's in your code, then I would probably just simply use the Filter property. Just my 2 cents...

 

[MajP]

This is why we encapsulate code in functions for utility.

Private Sub cmdsearch_Click()
  Dim strWhere As String
  strWhere = GetMultiLike(Me.txtMulti, "qualifying_Condition")
  If strWhere <> "" Then
    Me.Filter = strWhere
    Me.FilterOn = True
  End If
  Debug.Print Me.Filter
End Sub

 

[Db-why-not]

Sorry, I didnt add the code tags, but the computer i was using wasn't letting me select the code tags when I added the code.

I don't want to use a dropdown box for the search because there are too many keywords they could use. I am a newbie with the VBA and access. I got the original code from watching a utube video. It works good to search with 1 keyword, but I don't know how to allow the user to search with 2 words or more in the textbox. I had tried to edit the existing code I had that worked with 1 keyword, but wasn't sure how to do it.

Anybody recommenced a good book to learn VBA. I'm really only planning on using it for access and maybe in excel. Thanks.

 

[MajP]

Did you try my solution that does what you asked?

 

[Uncle Gizmo]

I did a short YouTube course on building a search form for MS Access here:-

https://www.niftyaccess.com/building-search-criteria/


Sent from Newbury UK

 

[MajP]

I did a short YouTube course on building a search form for MS Access here:-

Out of curiosity, how does that answer the OPs question of searching multiple keywords from a single textbox? Unlike my solution that shows how to do it.

 

[Uncle Gizmo]

Out of curiosity, how does that answer the OPs question of searching multiple keywords from a single textbox? Unlike my solution that shows how to do it.

Hi MajP,
I picked up on the OP's request here:-

Anybody recommenced a good book to learn VBA. I'm really only planning on using it for access and maybe in excel. Thanks.

and saw an excellent opportunity to promote my website yet again!

I also have a blog, and a set of videos to help someone beginning with VBA, and that is on my website here:- https://www.niftyaccess.com/vba-beginner/

 

[Uncle Gizmo]

Unlike my solution that shows how to do it.

That's a very Adam sounding comment. You're not Adam sneaking back in under a different profile are you?