Results 1 to 11 of 11

Thread: Searching Using a Query in Multiple Fields

  1. #1
    RossIV is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    15

    Searching Using a Query in Multiple Fields

    I am using Access 2010 on Windows 7 x64 and my database is based off of the Products Web Database from Office.com.
    I would like to be able to search for a product using any of the fields that are in the 'Products' table.

    I attempted to follow this tutorial video but I couldn't get it to work properly - whenever I tried to run the query, I didn't get any results unless I filled in the first field. None of the other fields had any effect. Unfortunately, I can't post the SQL source because this is a Web query, but I have attached a screenshot and have posted further details below.

    Form "SearchProducts"
    Eight fields are selected to correspond with what I want to search. Each is named appropriately (see screenshot). Also has a submit button which I can't get to work. I can't find a way to get the button to open the query datasheet view. I searched and many people suggested having the button use a macro (which I have to because I'm in a web database) to display the report view, but I want datasheet view.
    Click image for larger version. 

Name:	FormSearchProducts.PNG 
Views:	11 
Size:	10.6 KB 
ID:	13119

    Query "SearchProducts"
    Eight fields are selected to correspond with what I want to search. Each has a criteria, per the video, of the following:
    Code:
    Like "*" & [Forms]![SearchProducts]![txtProductCode] & "*"
    Like "*" & [Forms]![SearchProducts]![txtProductName] & "*"
    Like "*" & [Forms]![SearchProducts]![txtManufacturer] & "*"
    Like "*" & [Forms]![SearchProducts]![txtMPN] & "*"
    Like "*" & [Forms]![SearchProducts]![txtSupplier] & "*"
    Like "*" & [Forms]![SearchProducts]![txtSPN] & "*"
    Like "*" & [Forms]![SearchProducts]![txtCategory] & "*"
    Like "*" & [Forms]![SearchProducts]![txtLocation] & "*"
    I tried both configurations as seen in these screenshots - all criteria in the "criteria" field or the first one in criteria and the rest in "or".
    Click image for larger version. 

Name:	QuerySearchProducts.PNG 
Views:	11 
Size:	10.3 KB 
ID:	13118Click image for larger version. 

Name:	QuerySearchProducts1.PNG 
Views:	11 
Size:	8.4 KB 
ID:	13120

    I feel like it's something simple that I'm missing, but I could be wrong. As I mentioned above, I'm not limiting myself to this specific tutorial. I'm open to any suggestions as to how I can accomplish my goal of searching these eight fields in this table and filling in any number of fields and still getting a result if one is there.

    Please let me know if you need more information!

    Edit: I have started putting in the fields one-by-one into the Criteria instead of "or" and I have had more luck. For fields "ProductCode" through "SPN" everything works fine. If I try to search by category, I get results from many different categories instead of the one that I want.

    One glitch with searching by Manufacturer is that you have to enter the Manufacturer ID number (primary key - ID in table) instead of the name. How can I make it search with the name?

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,407
    I can't get your link to open right now - is it this tutorial: http://datapigtechnologies.com/flash...earchform.html

    All criteria should be on one row.

    If you want to search on manufacturer name, include the manufacturer table in the query by joining tables so the name associated with the ID will be available.

    Don't know what is happening with CategoryID and Location. Would have to analyze db.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    RossIV is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    15
    It's a YouTube link. Search "Microsoft Access Multi-Field Search Form" and you should find it.

    I've moved everything back onto the first criteria row.

    I added the Manufacturers table, but I still don't get any results when I search for the Manufacturer's name.

    The DB is HUGE (as in 100+ MB) and has a lot of private data inside. If you really think it would help I can strip down the private data and see how small I can get the file.

    Thanks for your help!

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,407
    Did you put the criteria under the name field instead of the ID?

    You could extract the relevent objects to another db file and provide that. Remove private data.

    Do you run Compact & Repair regularly?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    RossIV is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    15
    C&R dropped it down to 41MB. Working on splitting it into a separate DB now.

    If you are referring to my issue with the Manufacturer Name, the criteria is under Products.ManufacturerName which in the table is a lookup to the Manufacturers table in a dropdown.

  6. #6
    RossIV is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    15
    Here's the DB.
    Attached Files Attached Files

  7. #7
    RossIV is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    15
    Fixed the Manufacturer issue - I finally figured out what you were saying. I had to change where the query used Products.Manufacturer to Manufacturers.Manufacturer.

    I still don't get any results after I add MPN and forward.

  8. #8
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,407
    I tried modifying the lookup for the ManufacturerName field in Products table and I get an error: This lookup field is not supported by the server. Consider creating a new lookup field and then copying this lookup's values into the new field. I don't work with web databases so don't know why this is happening. However, it is not related to the query issue.

    Part of problem with the query is that the wildcard criteria all need to be on one row.

    Another issue is some of the records have null in the search fields (MPN and SupplierPartID). Need to handle the nulls. Options:

    1. additional criteria under each field that could have nulls: Is Null

    2. create field in criteria to convert Null and apply criteria under that constructed field.
    MPNAdj: Nz([MPN],"none")
    SupID: Nz([SupplierPartID,0)
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    RossIV is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    15
    The lookup issue is odd. It works fine in the main DB so it could have been something I did.

    I had already changed the criteria to be all on one row but forgot to reflect that in the uploaded DB.

    I tried the second option, but I'm not returning any results. (Screenshot)
    Click image for larger version. 

Name:	dbforforum.PNG 
Views:	10 
Size:	11.2 KB 
ID:	13147

    Edit: Oops. Just realized that I changed the Table for ManufacturerName back to Products instead of Manufacturers. That yielded results. Time for further testing!

    Edit 2: Looks like everything works except for searching by name for Supplier and Category. I tried adding the tables to the query but they didn't show a relationship. I'll try in the actual DB and let you know.

    Edit 3: In the main DB, everything works with the mods except for searching by category. In there I have that field as Categories.Category, so it *should* work.

    Edit 4: Once we fix the category issue, the last thing that needs to work is the button on the search form that should trigger the query. Thank you again for your help!

  10. #10
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,407
    Did you try the options to handle null fields?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  11. #11
    RossIV is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    15
    After I added the "IsNull" option, searching by category name works swimmingly. Silly me!
    Now we just need the button to work and I'm good!
    Thank you SO much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-13-2012, 03:53 PM
  2. Replies: 12
    Last Post: 05-07-2012, 11:41 AM
  3. searching for blank fields with a query
    By ironman in forum Queries
    Replies: 0
    Last Post: 03-04-2011, 01:48 PM
  4. Replies: 3
    Last Post: 02-08-2011, 08:25 AM
  5. Replies: 2
    Last Post: 05-25-2010, 01:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums