Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Search By Keyword

    First, let me say THANK YOU THANK YOU THANK YOU to Mr. Kohn for all his help so far.

    My question:

    I've got a form, called "frmSearchKeyword", where the user can enter a word or string of words to search the database. The field where the data is entered is a text box, called : 'txtKeyword'. The user enteres the data / word that they wish to query the main table, called : tblTask , in the field Task_Description, called : Task_Description.

    When the user enters what they want to query with, for example: appraisal, SGT, SFC, etc, they then click a command button, called: cmdSearch. This button then runs the query taking the information from the txtbox and running it through the entire db table field of Task_Description.

    The results from the tblTask that I want to have shown are:

    DateOriginated
    Task_Description
    Status

    These results are displayed in a list box with three columns right below the search criteria on the form, frmSearchKeyword

    I need to get that figured out first, and I'm working on it too, then I'm going to figure out how to make it so that the user can highlight the task they want and click a command button "Open" and it will open in the frmTask for editing purposes with all information in it, or double-click on the one they want and it will do the same.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    MyListBox.RowSource = "SELECT * FROM MyTable WHERE Task_Description Like %" & Me.txtKeyword.value & "%"
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    George,

    I'm going to try that since the things that I am doing aren't working. I tried like four different types of code and I couldn't get it to work. If I can pull the code from my old db, I'll try that (I think there will be too too many things to change though).

    I'll let you know.

    Thanks,

    Bryan

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Bryan,

    Also look at the frmSearchStatus and the changes I made. It does something like you described. You can tweak it a little but from what I've read, it meets the requirements you want it to do. You can use the same techniques in this form for you frmSearchKeyword form. Just clone it to frmSearchKeyword and make the chagnes.
    Last edited by pkstormy; 05-23-07 at 00:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The attachment might come in handy
    Attached Files Attached Files
    Last edited by gvee; 05-23-07 at 09:20. Reason: Attachement didn't attach :o
    George
    Home | Blog

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by georgev
    Code:
    MyListBox.RowSource = "SELECT * FROM MyTable WHERE Task_Description Like %" & Me.txtKeyword.value & "%"
    I've been using the * wildcard char in my SQL strings for a long time now, like:
    Code:
    MyListBox.RowSource = "SELECT * FROM MyTable WHERE Task_Description Like '*" & Me.txtKeyword.value & "*'"
    And it's worked fine for me. What's the difference between the two methods? Is one better than the other? Are they equivalent? Having I been doing it wrong for far too long?
    Me.Geek = True

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies - access uses asterixes (that's a mouthful!); whereas SQL Server uses percentage signs to denote a wildcard.

    A simple typo in my case
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    Bryan,

    Also look at the frmSearchStatus and the changes I made. It does something like you described. You can tweak it a little but from what I've read, it meets the requirements you want it to do. You can use the same techniques in this form for you frmSearchKeyword form. Just clone it to frmSearchKeyword and make the chagnes.

    Paul,

    I took a look at the form and the query for it. So to make the search form by keyword, I created a query that had the following:

    Task_ID / Task_Description / Date_Originated / Status




    ***UPDATE::::: The query coding worked! Well, EXCEPT for the fact that it won't search under different phrases. It worked searching for "test" but if I put in like "print", "close" , "open" or any other words it doesn't work.

    Thanks Paul and everyone else for the suggestions.
    Last edited by Grafixx01; 05-23-07 at 12:35.

  9. #9
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by georgev
    The attachment might come in handy

    Yeah, the attachment is some thing that I'd like to incorporate. Paul actually did some thing very similar to that for me. I got the code , or query , to work somewhat correctly but as stated above, it only returns results if I put in "test" but no other words. The results are shown but in a typical MS Access query but not in the listbox that I have on that form.

    You can view the file attached and see what I mean.

    Thanks for the input though.
    Attached Files Attached Files

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ...FROM tblTask" = WHERE((tblTask.TaskDescription) Like "*&strDescription&*"))
    Oh do come on now!
    Code:
    ...FROM tblTask WHERE tblTalk.TaskDescription Like '*" & strDescription * "'"
    George
    Home | Blog

  11. #11
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by georgev
    Code:
    ...FROM tblTask" = WHERE((tblTask.TaskDescription) Like "*&strDescription&*"))
    Oh do come on now!
    Code:
    ...FROM tblTask WHERE tblTalk.TaskDescription Like '*" & strDescription * "'"

    I didn't put the ' )) ' in there. I had it just like you did, well actually it was *"'"strDescription"'"* or some thing like that.

    I put in the code you suggested and now get a "type mismatch" error. I've never had one of those before. What is it? What does it mean? How to fix?

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by georgev
    Code:
    ...FROM tblTask" = WHERE((tblTask.TaskDescription) Like "*&strDescription&*"))
    Oh do come on now!
    Code:
    ...FROM tblTask WHERE tblTalk.TaskDescription Like '*" & strDescription * "'"
    George/Bryan, try...
    Code:
    ...FROM tblTask WHERE tblTalk.TaskDescription Like '*" & strDescription & "*'"
    A type mismatch is when something is expecting one type, and you give it another. So if a function expects a string and you give it a control, it'll yell at you, cause it's confused.
    Me.Geek = True

  13. #13
    Join Date
    Aug 2006
    Posts
    559
    nick,

    now I get an "object required". I wish I knew what that was also. I have no clue.

    the query I did in the db actually returned the result, it only returned "test" though. I have other records where in the field, Task_Description, I put in like 'print', 'award', 'close' and 'open' but it would not display any results in the MS Access query window with these. It would only return blank.

    The command button code is:

    On Error GoTo Err_cmdFind_Click

    Dim stDocName As String

    stDocName = "qryKeyword"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Me.List7.RowSource = "qryKeyword"

    End Sub

    The Query code/criteria is:

    Like [Forms]![frmSearchKeyword]![txtKeyword] & "*"

  14. #14
    Join Date
    May 2005
    Posts
    1,191
    Paste the following into your code, AND set your listbox RowSourceType property to Table/Query. PLEASE read through this code and understand it before you implement it.
    Code:
    Private Sub cmdSearch_Click()
    'this is the 'find us!' command button
    
    Dim strSQL As String
    Dim strDescription As String
    
    strDescription = Nz(Me.Text4, "")
    
    strSQL = "SELECT tblTask.Task_ID, tblTask.TaskDescription, " & _
                "tblTask.DateOriginated, tblTask.Status " & _
                "FROM tblTask " & _
                "WHERE (((tblTask.TaskDescription) Like '*" & strDescription & "*'));"
    Me.List7.RowSource = strSQL
    Me.List7.Requery
    
    End Sub
    EDIT: Attached zipped dB of functioning search window
    Attached Files Attached Files
    Last edited by nckdryr; 05-23-07 at 15:42.
    Me.Geek = True

  15. #15
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by nckdryr
    Paste the following into your code, AND set your listbox RowSourceType property to Table/Query. PLEASE read through this code and understand it before you implement it.
    Code:
    Private Sub cmdSearch_Click()
    'this is the 'find us!' command button
    
    Dim strSQL As String
    Dim strDescription As String
    
    strDescription = Nz(Me.Text4, "")
    
    strSQL = "SELECT tblTask.Task_ID, tblTask.TaskDescription, " & _
                "tblTask.DateOriginated, tblTask.Status " & _
                "FROM tblTask " & _
                "WHERE (((tblTask.TaskDescription) Like '*" & strDescription & "*'));"
    Me.List7.RowSource = strSQL
    Me.List7.Requery
    
    End Sub
    EDIT: Attached zipped dB of functioning search window

    Ok, so if I understand it...

    You took the information in 'txtText4' that the user puts in, denoted by the "", and set it as a string. then query the table, tblTask, to return the results of "Task_ID, Task_Description, Date_Originated and Status" based upon the string. The listbox shows the respective results.

    Did I get it right?

Posting Permissions

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