Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009

    Unanswered: Multiple search possible?

    I’ve seen many website recently giving possibility to search by many criteria in the same query, can’t it be done with Access? To make myself clearer (English not my 1st language) I attached two examples, one gives a drop down choice which cleverly allows different searches but with the help of a drop down menu, the second doesn’t even bother and permits you to search in the same field using many criteria.

    At present my MAIN tables look like this:

    Club ID
    Zip code
    Offers from Mon to Thu
    Weekend deals
    Children free
    Brunch deals
    Our feedbacks

    User ID

    Bookings ID
    User ID
    Club ID

    Relationships club ID to user ID to user ID

    How can I allow that kind of multiple criteria search to allow front end users to search within my club ID table by town, zip code, name, county, etc… within the same query? End result should show EVERY field included in that table. Ta
    Attached Thumbnails Attached Thumbnails Snap1.jpg   Snap2.jpg  

  2. #2
    Join Date
    Jan 2009
    Kerala, India

    Multiple search possible?

    After reading your question I tried something, but that may not be the exact solution. Any way this is what I have tried using the Orders Table from Northwind.mdb sample database. Created a Query by joining the following Text Fields by giving a space in between like:

    FieldsCombined: [ShipVia] & " " & [shipName] & " " & [ShipAddress] & " " & [ShipCity]

    and opened the Query in Normal View. Right-Clicked on the Column to Display the ShortCut Menu and entered an expression like the following to Filter records with the matching text in different location in the combined data (otherwise in different fields):


    5 records filtered matching the Text given with wildcard characters instead of Coma. The text matches at different locations in the record.

    The above example was only a trial run, this idea can be implemented in better way on Forms with little more ground work and VBA Code. (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Oct 2009
    sql is sql; while the syntax differs between products - this type of query is as possible in Access as any other db.

    don't get me wrong, there are legitimate reasons to go to other databases - but your query example isn't the reason.

    you can search on any of the fields; you challenge is to establish a form based user screen for the non-technical person to do it. and again - this challenge would exist with any other brand db.

    with Access you can have a simple continuous form - and teach the users that if they right click when in any column they can set filters or do sorts....

    or you could have a form with fill in the blanks that then provide variables that you use with vb to construct and sql string....

    I believe at Allen Browne's site he offers some query-by-form examples....

Posting Permissions

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