'Select All That Apply' Query with Checkboxes

[iwieldthehammer]

I have a database that I am building a query form for. This database contains thousands of products offered by hundreds of specialty food companies – my intent is to design a form enables the user to select three key words that relate to their product interest (such as ‘cheese’, ‘mild’ and ‘cheddar) as well as any number of geographic regions in which the user is looking for products. The form looks basically like the below:

What are three keywords which relate to the products you are interested in?

Keyword 1 _____
Keyword 2 _____
Keyword 3 _____

What geographic regions are you looking for products from:

__ New England
__ Mid-Atlantic

The goal here is that the user types in up to three words and then selects any number of geographic regions. My problem is, I cannot get the OR statements to work in the query. In other words, I can only get it to select records if I choose one (and ONLY one) geographic region, using the query below (I just have two regions in there for now, New England and Mid-Atlantic):

SELECT Product.[Booth #], Product.Company, Product.State, Product.Country, Product.Phone, Product.Website, Product.[Product Name]
FROM Product
WHERE (((Product.[Booth #])<>"0")
AND
((Product.CustomColumn1description) Like "*" & [Forms]![SearchF]![Keyword1] & "*" And (Product.CustomColumn1description) Like "*" & [Forms]![SearchF]![Keyword2] & "*" And (Product.CustomColumn1description) Like "*" & [Forms]![SearchF]![Keyword3] & "*"))
AND
(
((Product.[New England (CT, ME, MA, NH, RI, VT)]) Like IIf([forms]![SearchF]![New England]=-1,[forms]![SearchF]![New England],"*"))
AND
((Product.[Mid-Atlantic (NJ, NY, PA)]) Like IIf([forms]![SearchF]![Mid-Atlantic]=-1,[forms]![SearchF]![Mid-Atlantic],"*"))
);

If I try replacing the last AND in the above query (the one between the geographic regions of New England and Mid-Atlantic) with an OR, as below, the query will ignore the geographic selections unless I select ALL of the options (in this case both New England and Mid-Atlantic checkboxes):


SELECT Product.[Booth #], Product.Company, Product.State, Product.Country, Product.Phone, Product.Website, Product.[Product Name]
FROM Product
WHERE (((Product.[Booth #])<>"0")
AND
((Product.CustomColumn1description) Like "*" & [Forms]![SearchF]![Keyword1] & "*" And (Product.CustomColumn1description) Like "*" & [Forms]![SearchF]![Keyword2] & "*" And (Product.CustomColumn1description) Like "*" & [Forms]![SearchF]![Keyword3] & "*"))
AND
(
((Product.[New England (CT, ME, MA, NH, RI, VT)]) Like IIf([forms]![SearchF]![New England]=-1,[forms]![SearchF]![New England],"*"))
OR
((Product.[Mid-Atlantic (NJ, NY, PA)]) Like IIf([forms]![SearchF]![Mid-Atlantic]=-1,[forms]![SearchF]![Mid-Atlantic],"*"))
);

I would appreciate some help with this. I would like the query to take the first two clauses in the WHERE statement (which work fine) and then, if the user selects a geographic region, add in that criteria. Is it the Like IIf statement that is causing the issue? If so, what are the alternatives to making the checkboxes work?


Thanks!

 

[Uncle Gizmo]

How about using a multi select listbox instead of checkboxes? A listbox offers several advantages, it is easy to add new entries. Adding new entries will not affect the way you build your SQL statement...

 

[iwieldthehammer]

@ Uncle Gizmo - Will the list box enable the user to select multiple options? From what I have read the checkboxes are the only way to select all that apply - could you explain how your suggestion would work? Thanks!

 

[Uncle Gizmo]

Will the list box enable the user to select multiple options?

Yes; as I said:- "multi select listbox"

 

[iwieldthehammer]

Yes; as I said:- "multi select listbox"

Can you explain how I would do that?

 

[Uncle Gizmo]

I'll be able to put something together for you later.

To give a bit of a boost, if you could provide a sample of your database, it only needs a few records and you don't need any of the other stuff, just the particular forms and tables you've got issues with. If you could post that then it would be much quicker to come back with an answer. I can't do anything till a bit later.

Someone else may step in, although it's Christmas, and most people are drinking mulled wine, Baileys, Carlos 1, well that's what I'd like to be doing anyway!

In the meantime if you're bored you might want to look at my YouTube playlist here:- Advanced Search Criteria - Nifty Access where I demonstrate some useful techniques for turning basic SQL statements into nice-looking functions which operate in an object orientated way.

Could save you a bit of time and trouble and tidy up your code. Not saying you should do that, your code is perfectly fine... It's just you might want to take it to another level, or you might not! Here's a good road map if you desire to, so to speak....

 

[Pat Hartman]

Here's an example that uses a multi-select listbox.

 

[iwieldthehammer]

To give a bit of a boost, if you could provide a sample of your database, it only needs a few records and you don't need any of the other stuff, just the particular forms and tables you've got issues with.

I have attached a sample database that is similar to what I am working with. As you can see, you can add up to three keywords, then select New Products or SOFI Award winners or both. What you CANNOT do (and what I would like to do) is select records that meet the Keyword criteria but that are EITHER New Products or SOFI Award Winners.

For example if you type 'Artisanal' into Keyword 1 (leaving checkmarks blankl) you get 8 records. If you leave Artisanal and select 'New Products', you get 4. If you deselect 'New Products' and select 'SOFI Awards' you get 4. This is all fine so far. The issue comes with the next statement - if you leave Artisanal in and select BOTH New Product and SOFI Awards, you get 3 records - those products that have BOTH these designations. What I need is to select the 4 records in the database that are EITHER SOFI Award winners or New Products. See the difference? I know you recommended the listbox solution, but that seems overly complicated - if you check out my SQL, is it just a matter of an errant parentheses or something? It seems like the query should be simple, but it is not working.

Happy to try the Listbox, but just wanted to put it out there that it might be an easier fix than that.

 

[Pat Hartman]

When you create complex conditions that include AND, OR, and NOT, you will almost certainly need to use parentheses in order to control how the expression is evaluated.

a OR b AND c

is evaluated as -- a OR (b AND c) -- because AND takes precedence over OR

so if you really meant -- (a OR b) AND c --- you need the parentheses to force the expression to be evaluated as you intend.

PS:
Your query has lots of extraneous parentheses INCLUDING some that are actually part of a name so it is almost impossible to interpret. It is really poor practice to create object names that include special characters and spaces. Use either CamelCase or the_underscore to separate words.

If you add -- New England (CT, ME, MA, NH, RI, VT) as a control on a form and put code in it's BeforeUpdate event. Access will name the field:

New_England__CT__ME__MA__NH__RI__VT_

Notice how Access has to remove all the offending characters.

How do you think these two would work?
Sales Amt %
Sales Amt $

 

[iwieldthehammer]

When you create complex conditions that include AND, OR, and NOT, you will almost certainly need to use parentheses in order to control how the expression is evaluated.

Do you think the parentheses are the only issue with this code? The only reason it is that way is because I was experimenting with various permutations and trying to isolate various parts of the code so that it would function properly. Obviously I went too far...

How would you rewrite the code in the sample database above so that it does what I want it to do? I really appreciate your help!

 

[Uncle Gizmo]

Sorry I can't do anything with the sample database you provided it's just crap! If that's anything like your real Database then you've got a lot of issues to fix before you develop your search function.

I suspect the two Fields which are boolean, SofiAwards & New Product should be in their own boolean table, but I can't really judge that until I know the reality of your situation....

Another problem with your database is, you have one field which contains product tags similar to this structure:- Hard Cheeses|Swiss Cheeses|Domestic Cheeses. That's so wrong! So I think it's going to be hard cheese, from me anyway!

I'm sure someone can fix what you've got already and make it work, however you're on dodgy ground, your problems will get worse and worse until eventually you will need complicated VBA to fix them....

Would be a very good idea for you to take a step back and discuss your database structure before you proceed any further!

 

[Mark_]

From what you are describing you have a user interface error.

For myself, I'd allow the user two distinct sets of options they can add "Restrict" and "Require". "Restrict" means it must have ALL of the values you entered (AND clause) while "Require" would be "Must meet at least one of" (OR clause). Having three free form entries without a way to ensure which order users enter data (or say they want all cheese from France, so they won't need all three) will make your life very difficult when you try to code for this.

I haven't looked at your database, but from this description I could tell there is a major issue in how your laying it out. I'd also check with the end users to see what they really will be asking for. Cheese-Mild-Burgandy OR Champagne wouldn't work in your current system.

 

[Pat Hartman]

Do you think the parentheses are the only issue with this code?

No. IIf()'s have THREE parts - condition, true result, false result
You are not providing all three.

I can't rewrite the code because only you know how you want the AND and OR operators to work. Cleaning up the code is a good start. Changing the names isn't required but is strongly suggested.

 

[isladogs]

No. IIf()'s have THREE parts - condition, true result, false result
You are not providing all three.

I can't rewrite the code because only you know how you want the AND and OR operators to work. Cleaning up the code is a good start. Changing the names isn't required but is strongly suggested.

There is plenty wrong with this code but it does have both true and false parts to the IIF statements. The idea of the false part is to show all records

 

[Mark_]

"Product Tags" SHOULD be its own child file. Looking at how you are storing the data you are effectively putting individual field values into a hot pile. I would hate to try to either enter this data, verify it, or ensure consistency.

If you move it to a child file you can then use a look up (to a dictionary file) to get your tag values. This would let you then use a combo box for matching.

 

[JHB]

I have attached a sample database that is similar to what I am working with. As you can see, you can add up to three keywords, then select New Products or SOFI Award winners or both. What you CANNOT do (and what I would like to do) is select records that meet the Keyword criteria but that are EITHER New Products or SOFI Award Winners.
...

I think you need to build the "SQL" string using code.