Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Unanswered: How to search mulitple fields in access

    I am currently making a database of Health Services and Helath Professionals need to search the database in order to better help their patients. I need to do a search where the person can searh two fields, first be location and then by keyword, I currently have two seperate search butttons one for locations and other by keyword, i like to do a multiple search that consists of the two combined. Services that are offered in commuinty A and has keyword B.

    NewfieBullet

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    build yourself some SQL in code:

    strSQL = "SELECT * FROM yourTable WHERE ((1=1) "'thanks Rudy
    if not isnull(locationBox) then
    strSQL = strSQL & "AND ([location] = " & Locationbox & ") " 'example for a numeric value
    endif
    if not isnull(keywordbox)
    strSQL = strSQL & "AND ([keyword] = '" & keywordbox & "') " 'example for a string value
    endif
    strSQL = strSQL & ");"
    whateverYourFormIsCalled.recordsource = strSQL

    izy



    later: missing space in & keywordbox& fixed above
    Last edited by izyrider; 03-30-04 at 14:39.
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2003
    Posts
    268

    Similar Tool

    I actually did something very similar myself. The users needed to be able to search by ID, name, City or Type or any combination of the three. So I dynmically created a select statement, then passed it as the recordsource of the subform. Attached is the example. Hope this is helpful.
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2004
    Posts
    51

    Cool Re: Similar Tool

    Hey Thanks for your reply:

    I tried to use the code from the database and when i made new form and put the code in the cmdExecute it gave me this error "addQuotes" sub or function not defined?


    Any suggestions?

    NewfieBullet

  5. #5
    Join Date
    Dec 2003
    Posts
    268

    AddQutoes

    AddQutoes is a function I wrote to add "" around strings that are passed to a SELECT string. I find if working with a lot of different dynamically created SQL strings it is easier for me to read i.e.

    "INSTERT INTO tblTable VALUES("""&textbox1& """, """" & textbox2 & "")"

    vs.

    "INSERT INTO tblTable VALUES (" & addquotes(textbox1) & ", " & addquotes(textbox2) ", )"

    It is a little bit longer, but you can tell when one is stopping and the other is starting.

    It also makes troubleshooting the errors in the creation of the string a little easier to do.

    If this is something you want to use here is the code. Paste this into a module

    public function AddQuotes(str as string) as string
    addquotes = chr(34) & str & chr(34)
    end function

    Thats it..... The code is also under Module1, along with some other hacking I was working on that I abandonded.

    Hope that helps.

  6. #6
    Join Date
    Mar 2004
    Posts
    51

    Cool

    Hello:
    I totally redid my forms to match the one u sent me and called them the same name's etc.. i am getting an error on the line below and its says method or data member not found and i am assuming that it is the form but ive checked the names and to no avail

    Me.sfrmStart.Form.RecordSource = getSQL

    sfrmStart says that method or data member not found and the sfrmStart is highlighted

    any suggestions

    sorry but im not very flluent is VB

  7. #7
    Join Date
    Dec 2003
    Posts
    268
    This is pointing to a subform on a form It is looking on form StartUp which contains a subform sfrmStartUp. If these don't exist on your tool then the error will occur. Just make sure that you have a subform with this name. This s hould fix the problem

  8. #8
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Or for something else to check out the free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  9. #9
    Join Date
    Mar 2004
    Posts
    51
    Hello:

    Thanks for the repy, Ive havn't been doin the database because all of the civil service is on strike. I hope the strike will soon be over and i can get back at the database. I will look at this sample database and see if it would help. I would like to thank you for your time.

    NewfieBullet

  10. #10
    Join Date
    Mar 2004
    Posts
    51
    Hello Everyone:

    I am finally back to work, after 28 days of stirkeing, good to be back, I would like to thank everyone for their input but i still dont seem to have anyluck getting it working.

    I have two tables, one called "Group Info" and the other called "Service Info", In "Group Info" i have a field called location, in "Service Info" in have a field called Keyword. As of now on form called Keyword Search there is a text box, u type in the respective keyword and hit the "GO" command button and any fields with the keywork u typed will be displayed on a form called Search by Keyword, to the right of the text box is a combo box, it has a list of all of the town in the area we cover and hit the drop down arrow and select the area u disire and hit "GO" in the form Search by Keyword all the fields with that location is displayed.

    I cant figure out a way to type in the desired keyword and select the desired location and hit the command button and any results with the keyword (for instance) and location of margaree will be displayed on the search by Keyword form. Right now it only displays either the keyword or Location


    Any help would be greatly appreciated

    Jason

  11. #11
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    What about having two unbound text boxes on your form for each criteria and have a query with reference to the those two text boxes in the appropriate fields.

    For example I have the following in the criteria for a field in one of my queries and E, P etc are unbound text boxes on the form PrintandClose

    [Forms]![PrintandClose]![E] Or [Forms]![PrintandClose]![P] Or [Forms]![PrintandClose]![R] Or [Forms]![PrintandClose]![S] Or [Forms]![PrintandClose]![X] Or [Forms]![PrintandClose]![I] Or [Forms]![PrintandClose]![O]

    If I did similar in another field then the query would only return records where both fields matched the entry in the text boxes on the form.

    Put an OpenQuery macro on the form

    Mike

  12. #12
    Join Date
    Mar 2004
    Posts
    51
    Hello:

    I seem to understand what you are saying but dont quite follow what this means "[Forms]![PrintandClose]![E] Or [Forms]![PrintandClose]![P] Or [Forms]![PrintandClose]![R] Or [Forms]![PrintandClose]![S] Or [Forms]![PrintandClose]![X] Or [Forms]![PrintandClose]![I] Or [Forms]![PrintandClose]![O]"

    Is it what u put in your criteria field in your query?

    I have 2 text boxs on a form and one is called txtDualkey. In the query in have selected my fields from my table and in the criteria section i want to pull the word from my txtDualKey text box on the form Keyword Search. I cant seem to get it to use the word that i typed in the textbox.

    Is this right i have a field called Keyword in a table called Sercice Info. When a person types lets say child in the txtDualkey word box and hit run query it should take the work and use it in the query to pull those records that have child in their keywords.

    Hope its not too confusing.

    Any help appreciated.

    Jason

  13. #13
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I seem to understand what you are saying but dont quite follow what this means "[Forms]![PrintandClose]![E] Or [Forms]![PrintandClose]![P] Or [Forms]![PrintandClose]![R] Or [Forms]![PrintandClose]![S] Or [Forms]![PrintandClose]![X] Or [Forms]![PrintandClose]![I] Or [Forms]![PrintandClose]![O]"

    Is it what u put in your criteria field in your query?


    Yes. E, P, R, S, X, I and O are the name of unbound text boxes on the form PrintandClose.

    I have 2 text boxs on a form and one is called txtDualkey. In the query in have selected my fields from my table and in the criteria section i want to pull the word from my txtDualKey text box on the form Keyword Search. I cant seem to get it to use the word that i typed in the textbox.

    In the criteria of your query and in the appropriate field I would have:

    [Forms]![Myform]![txtDualkey]

    Now let's assume the fioeld in question is whether an insurance policy is paid monthly, quarterly, half yearly or yearly and we say that the field entries are M, Q, H or Y. Then if you entered H in the text box txtDualkey and the opened the query the query would be restricted to all the people who pay half yearly.

    Then you might have a second text box that is going to be used for male or female which we say are in the Sex field as F or M. In the query criteria for the field Sex you would have [Forms]![Myform]![SexTextbox]

    Thus if you entered M in the SexTextBox and H in the txtDualkey the query would display all the people are male and who pay their premiums half yearly.

    Is this right i have a field called Keyword in a table called Sercice Info. When a person types lets say child in the txtDualkey word box and hit run query it should take the work and use it in the query to pull those records that have child in their keywords.

    The text boxes on the form where the criteria go are to be unbound text boxes.

    The example I gave of my own with the E, P, R, S, X, I and O is because those seven letters ate the possible entries in the field. Clicking on each of the textboxes automatically inserts an E, P, R, S, X, I or O. So if I click on the textbox for P, S and O then the query will return all the records that have P, S or O entered

    Mike

  14. #14
    Join Date
    Mar 2004
    Posts
    51
    Hello Mike:

    That is the way ive been putting the info in the criteria box but still wont display any results at all.

    If i put Like "*Child*" in the criteria box for Keyword and put Like "*Margaree*" in the criteria for Location,it will display the results but when i put Forms![Keyword Search]![txtDualkey] in the criteria box and put Forms![Keyword Search]![txtDuallocation] the query runs with no results at all.

    Jason

  15. #15
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Jason

    I am running Access 95. I wonder if there is a difference if you have a later version. Which version are you running. Also, see the end of this post in bold type as it might be the answer. i only just thought of it as I was writing thios posting.

    But let's make sure it is being done right and perhaps start at a "more sure level"

    I have this in some of my queries [Forms]![12ProspectT]![NameNumber] criteria. In this case NameNumber is a field not an unbound text box and the [Forms]![12ProspectT]![NameNumber] is placed in the criteria for the field NameNumber.

    A QueryOpen macro on the form will open the query showing all the records for the person whose form is open. In my case I am in the insurance business and that query might display all the insurance policy benefits that persons owns.

    If you open such a query with a criteria as [Forms]![12ProspectT]![NameNumber] and with the form closed you well get the Enter Parameter Value box open and if you enter the value the query will open.

    So, try a query like the above with only one field and referencing an actual field in the form as distinct form an unbound text box.

    By the way, my first example I gave you of

    [Forms]![PrintandClose]![E] Or [Forms]![PrintandClose]![P] Or [Forms]![PrintandClose]![R] Or [Forms]![PrintandClose]![S] Or [Forms]![PrintandClose]![X] Or [Forms]![PrintandClose]![I] Or [Forms]![PrintandClose]![O]

    is entered as criteria in the "type of insurance policy benefit" and that query also has [Forms]![PrintandClose]![NameNumber] as criteria for the NameNumber field. In other words it is doing as you want, that is, applying criteria to more than one field.

    If I open the query with the form closed then the Enter Parameter Value box will open and request E, P, R and so on and then NameNumber.

    The other thing you might need to do is to save the record on your form after you have entered into the text boxes. In my case I open the query via a macro and SaveRecord is the first action from the DoMenuItem action. It seems your query is opening but with no records being displayed then it is like the text boxes were empty.

    I just made a query where criteria is [Forms]![MasterForm]![Text1505] and that was placed in the CL Surname field of the query. I then opened the MasterForm and type a surname into Text1505 and then open the query direct from the Data Base window and no records showed. I then closed the query and did Save Record from the tool bar and then again opened the query from the Data Base window and the record was there.


    Mike

Posting Permissions

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