Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With A Logical Search Button (Access 2003)

    I'm creating a db of personal home video DVD's and am stuck on one issue. My main switchboard form has a button which I can use to locate video clips on various DVD's which contain certain subjects. I use the button to execute a select query and show a table of results. The query uses:

    Like ("*" & [Search Movie Clips for?] & "*")

    in the Criteria line of the Subjects field. When I click the button, a dialog box asks "Search Movie Clips for?". I then enter a subject (like "John") and the table appears with all the clips that have "John" in the subject. So far so good!

    However, I sometimes I would like to be able to enter a logical expression (like "John OR Smitty") and be able to get a resulting table of clips with either "John" OR "Smitty" in the subject field.

    Is this possible?

    I'm relatively new to Access but have years of experience in other database programs.

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With A Logical Search Button (Access 2003)

    SQL (the query language used by Access) doesn't work that way: the criteria for two search terms would be

    Like "*" & [Search Term 1] & "" Or Like "*" & [Search Term 2] & "*"

    You cannot enter the keywords Like or Or in the parameter prompt, so this becomes unwieldy. Fortunately, Access has several nifty filtering features built in. You must first display the table or query, or preferably, a form. The filter options then become available through the Records Filter submenu, the toolbar and the right-click menu.

    - Filter by Selection. This acts as if you entered a Like ... condition.
    - Filter by Form. You can specify conditions on several fields, and there is an Or tab at the bottom to let you specify other conditions.
    - Advanced Filter. This lets you specify conditions the same way you do in query design.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With A Logical Search Button (Access 2003

    Hans,
    Thanks for the prompt reply. I know about the filters and they work great but I was using this project as a learning tool and really wanted to know how much control and flexibility buttons could have. I guess I can't do what I wanted (ie: enter two or more "filter" criteria via a simply button.

    The "2 search term" criteria line you supplied worked well but, of course, it would be of limited use. BTW - you left out a "*" in the quotes between the "&" and the "Or". I guess I'm learning something!!

    Again thanks for taking the time to help.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With A Logical Search Button (Access 2003)

    Tony

    Check out Forms: Adding a Search form to a database

    http://www.mvps.org/access/forms/frm0045.htm

    you may be able to extract code to do what you want

    Lets us know how you make out

    HTH, John

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With A Logical Search Button (Access 2003

    John,
    I followed your suggestion and got the form query to do what I wanted. As I mentioned in my original post, I'm new to Access (and SQL) and I'm afraid that I have a lot to learn but this example will supply me with a great tool to help understand the process.

    Thanks for your help.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With A Logical Search Button (Access 2003

    Tony

    I was new to Access several years ago, interviewed several forums and stuck with Woody

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With A Logical Search Button (Access 2003

    John,

    I appreciate the links. I see what you mean about Woody's - I have been a member for years (in other forums) and find it a great tool for learning.

Posting Permissions

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