Searching Using a Query in Multiple Fields

[RossIV]

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.


Query "SearchProducts"
Eight fields are selected to correspond with what I want to search. Each has a criteria, per the video, of the following:

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".


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?

[June7]

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.

[RossIV]

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!

[June7]

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?

[RossIV]

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.

[RossIV]

Here's the DB.

[RossIV]

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.

[June7]

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)

[RossIV]

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)


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!

[June7]

Did you try the options to handle null fields?

[RossIV]

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!