Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Question Unanswered: Display report results based on search form

    Hi,

    I have an access report that display data based on unbound search form, which is working fine if the user type the full description or the product ID. What I would like to do is to allow the user to type any part of the description or the first a few digits of the product Id to retrieve similar products. I tried to use Like in the report SQL statement but unfortunately did not work for me. I attached the database and here are the steps to perform the search.

    1. Double click on VendorTps report - Search form should prompt for search input.

    2. Type 07619385 in the TPS product ID and click Ok - this will display the report result

    Vendor ID: 0000000223 COMPUTER SYSTEMS COMPANY
    Product ID: 07619385 CSC PAPERS TERM LICENSE.


    I wanted to be able to search on PAPERS or 07619 and get list of products on the report.

    I'd appreciate any help!!!

    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    Looked at your database and the only thing that I know of, is for you to use an SQL statement with the Like operator as in:

    WHERE [Code] Like "*PAPER*";

    This will return all records that contain the word "PAPER"

  3. #3
    Join Date
    Aug 2004
    Posts
    3
    Quote Originally Posted by ineuw
    Looked at your database and the only thing that I know of, is for you to use an SQL statement with the Like operator as in:

    WHERE [Code] Like "*PAPER*";

    This will return all records that contain the word "PAPER"
    Thank you for your effort. "PAPER" just search example, I have over 3000 products and the users need to be able to search on any part of these products description using the search form.

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi elkhashab,

    I fixed your database for you. Take a look and see if you like the way it is now done. There is a ComboBox to select from and as you type in it the items closest to your input constantly come up. Then simply click on the Open Report button to show the record you just selected.

    have a nice one,
    Bud
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2004
    Posts
    3
    Quote Originally Posted by Bud
    Hi elkhashab,

    I fixed your database for you. Take a look and see if you like the way it is now done. There is a ComboBox to select from and as you type in it the items closest to your input constantly come up. Then simply click on the Open Report button to show the record you just selected.

    have a nice one,
    Bud
    Thanks Bud, my main concern is how to search on any part of the description, most of the time the end user does not know the product id.

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by elkhashab
    Thanks Bud, my main concern is how to search on any part of the description, most of the time the end user does not know the product id.
    I modified it so that you can search each field with the ComboBox and then pull up the report from there. As you type in the box it pulls each closest match.

    have fun,
    Bud
    Attached Files Attached Files

  7. #7
    Join Date
    Aug 2004
    Posts
    3
    Thanks Bud, I was able to fix the SQL so that it can be searched based on any part of the description and the product id . I attached the new db but still strugling with the Vendor ID.

    By the way, DoCmd.Maximize statment in OnClick event - open report button does max. the form not the report this happen when you open the report directly.

    Thank you for all your help.
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Quote Originally Posted by elkhashab
    Thanks Bud, I was able to fix the SQL so that it can be searched based on any part of the description and the product id . I attached the new db but still strugling with the Vendor ID.

    By the way, DoCmd.Maximize statment in OnClick event - open report button does max. the form not the report this happen when you open the report directly.

    Thank you for all your help.
    I d/l your last attachment but didn't see how the search feature was supposed to work. What if a user is new to your program and has no idea what all the lenghty numbers are? That was why I created the ComboBoxes to give the end user a choice to select from. Just a matter of making it very simple for someone who knows nothing of it. But it's good to know you're satisfied with the results. That's what we're all here for.

    have a nice one,
    Bud

Posting Permissions

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