Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Finding

  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: Finding

    First sorry but im an uber-n00b

    My company uses a database to keep track of video stock, the old database has a "find" button which launches a "Find and replace" window (the same as if you were to press Ctrl+F) then you have to select "whole database" and "any part of field" in order to search for a term in the database.

    This is obvoiusly a pain and i have tried making a form based input to get around this, but doing this you can only have a query running on one field in the database.

    Can anyone help?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    could you please be more specific as to how you would like this to work?

    The quick answer is you may search within a recordset using the SQL function "LIKE"

  3. #3
    Join Date
    Oct 2003
    Posts
    15
    it needs to work in the same way as the find and replace box, but without the user having to select "whole database" and "any part of field" (this is always the case), ideally this will be done from a form, but that is just cosmetic and doesnt really matter

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What sort of table structure do you have setup?

    Assuming all the title names are in the same field of a table (and if they're not you should consider a schema restructuring), you can make a form with a text box, and a listbox.

    Ask the user to input a string they'd like to look for in the text box, then you can dynamically set the filter for your listbox to return only records containing the requested string.

    The LIKE function that I was mentioned could be used something like this:

    String to find: Gump

    SELECT *
    FROM yourTable
    WHERE film_name LIKE '%Gump%'

    The %'s act as "wild cards". using them before and after the string means EVERY title that has Gump in it will be returned.

    You can parameterize that query as well, but I won't get into specific methods until you let us know what direction you'd like to go.

  5. #5
    Join Date
    Oct 2003
    Posts
    15
    It is a single table for this specific section of the databse, the thing is i dont just want it to look in one field, i need it to look across every field

    I dont understand a lot of your post, can you advise me in english, including where to click etc please.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    haha.

    You are obviously quite frustrated with this issue, and I apologize if my advice was hazy.

    However, I do question why you would need such a function.. What practical environment exists were you need to search every field in a database for a given string?

  7. #7
    Join Date
    Oct 2003
    Posts
    15
    Its a tape catalogue, my company has various video cassettes both filmed by us and others, Each tape has a title and subtitle, an individual number, and a description field.

    For example, if a tape has more than one programme or is "rushes" (unedited footage) it may have more than one different thing on the tape, hence the least important of the two would be listed in the description field, as opposed to the title. Also for "rushes" tapes, there may just be one 10 second long good clip that is listed in the description field taht we may want to re-use.

    Also we have compilation tapes which include more than one programme, so if we were to look for one programme on a comp tape, the individual programmes would be listed in the description field not in the title or subtitle.

    And please do not suggest changing the database format, it has over 2000 entries and im trying to avid that at all costs. You are quite right, im very frustrated with this project.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, so there's 4 fields that need to be searched? That's not so bad...

    You can have your text box and use some chained LIKE comparisons to return your desired result set:

    SELECT *
    FROM yourTable
    WHERE title LIKE '*" & txtBox & "* OR subtitle LIKE '*" txtBox & "*' OR member LIKE '*" & txtBox & "*' OR description LIKE '*" & txtBox & "*'"

    You can then trigger a requery from either an OnClick event from a "search button" or the On enter event of the textbox.. either way

  9. #9
    Join Date
    Oct 2003
    Posts
    15
    Right, now step by step where i type that stuff because im a video editor not a computer technician, im using access 2003

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by ravislayer
    Right, now step by step where i type that stuff because im a video editor not a computer technician, im using access 2003
    Please keep your frustration in check, I enjoy helping folks out, but it's much easier if they remain calm

    The code that I posted was SQL that you can attach to a listbox or where-ever you'd like to see all the matching results displayed.

    You could create a text box, a command button, and a listbox on a form to do this.

    The text box would be for your user (or you) to enter a string you'd like to search for. Then you could press the button, and any relevant results would be dumped into the list box.


    So you need to put some code in the "onClick" event of your command button. You can get there by right-clicking a control select preferences then go to the Event tab, and clicking the elipses on the right of the filed, then double clicking Code editor.

    In the Onclick, you can write some code that will populate your listbox:

    YourListbox.RowSource = "SELECT * FROM yourTable WHERE yourField LIKE '*" & yourTextBox & "' OR nextField LIKE " etc etc

    Then requery the List box:

    yourListBox.Requery

    done.

  11. #11
    Join Date
    Oct 2003
    Posts
    15
    right.....ok......thank you im gonna go try this ill probably be back

  12. #12
    Join Date
    Oct 2003
    Posts
    15
    OK, thats good, thanks very much. How do i modify this code so that it displays in datasheet view?

  13. #13
    Join Date
    Oct 2003
    Posts
    15
    and i need to do something with the '*'? dont i?

  14. #14
    Join Date
    Oct 2003
    Posts
    15
    i get a compile error syntax erro, help, plz!!!!

  15. #15
    Join Date
    Oct 2003
    Posts
    15
    Here is the code in full

    Private Sub Command2_Click()

    List3.RowSource = SELECT * FROM Tape WHERE Title LIKE '*" & Text0 & "' OR subtitle LIKE '*" & Text0 OR customer LIKE '*" & Text0 OR Notes/Description LIKE '*" & Text0

    List3.Requery
    End Sub

Posting Permissions

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