Multiple Field Query - Criteria problem - AND (1 Viewer)

[mjdemaris]

Hey all.

I've got some multi-field searches working off of a single textbox, but the issue I have is narrowing down the records.

I search four fields: Internal Part Number, Description, Vendor Part Number and Barcode. However, there is a fifth field that I would like to act as a master filter, such as: Room Number.

So, if I search for 12345, I only want those records with matching data (from any of the four fields) but also only in Room #1 or 2, depending on which Room that person wants to search.

Currently, both multi-search options(Rooms 1 & 2) return items from both rooms.

The WHERE clause:
WHERE ((((Bins.Warehouse)='PIT') AND ((SupplierPartNums.PartNumber) LIKE '*" & theform!txtSearch.Text & "*')) OR (((Items.MasterNum) LIKE '*" & theform!txtSearch.Text & "*')) OR (((Items.Item) LIKE '*" & theform!txtSearch.Text & "*')) OR (((SupplierPartNums.Barcode) LIKE '*" & theform!txtSearch.Text & "*')))

But, the AND doesn't do what I thought it might.

In this case, MasterNum = Internal Part Number, Item = Description, and Supplier fields are self explanatory; and PIT is the name of a Room. I've tried parentheses in various spots, no dice.

Trying to create a query that will provide the wanted results, but no luck so far.

What does work: when I search only one field at a time - i.e. Vendor Part Number AND Room "PIT". Then I get only those items in the PIT.

Thanks.

 

[sneuberg]

Sometime it helps to take out all of the parenthesis and then put them back in. I believe you want the ORed fields to have the same weight as the Bins.Warehouse so I thing you just need parenthesis around the ORed part like:

WHERE Bins.Warehouse='PIT' AND (SupplierPartNums.PartNumber LIKE '*" & theform!txtSearch.Text & "*' OR Items.MasterNum LIKE '*" & theform!txtSearch.Text & "*' OR Items.Item LIKE '*" & theform!txtSearch.Text & "*' OR SupplierPartNums.Barcode LIKE '*" & theform!txtSearch.Text & "*')

 

[mjdemaris]

Snueberg,
Brilliant! It's interesting how Access puts the parentheses in the places it does and how many are used! After making your suggested changes, I observed the query in design view, and to my surprise (shock!) I learned something new. So, I need to place the "master" filter on each line of the OR criteria, so that no matter what field is returning a record, all data will have to also match the "master".

Thanks!