Search Form - Using OR Operator?

[rdc175]

Hi,

I have data of the following type:

First Name
Last Name
Company
Product A
Product B
Product C
Product D

where each "Product" field is either blank or has the value "x" in it. How can I build a search form that has the following functionalities:

1. Show who is using a single product (i.e. show all customers with "x" in Product A
2. Show who is using an "AND" combination of products (i.e. show all customers with "x" in Product A and B and C)
3. Show who is using an "OR" combination of products (i.e. show all customers with an "x" either in Product A, Product B, or Product C, or any combination of the 3

I would like to build this using unbound text boxes and a query button, rather than combo boxes, if possible.

Thanks a lot in advance - unfortunately I'm a total Access noob and quite stumped by this, and I appreciate all the help I can get! :banghead:

 

[CJ_London]

your data is not normalised which means you would need a painfully complex query to get your result - plus if you add another product, not only will you need to modify the table, but all resultant queries, forms and reports.

databases work to completely different rules from Excel. You need to learn about normalisation - In simple terms it means a) store data only once, b) don't store calculated values and c) don't build data into the structure

you are breaking rules a and c.

from your table, looks like you might need 3 or 4 tables - one for contacts and/or one for company, one for products and then a linking table