Issues with SQL Dynamic Query Based on User Input for Access

Question

I have a table, Contact Info

I have a form for user input, frmSearch

I have a query that is the subject of my question, frmSearchQuery

The table has data about residents which are First, last, phone, email, address, .... And about 25 mixed education and skills.

---

Use Case

The idea is that my client can check some boxes in a fool-proof form, and it'll spit out residents that mach the selected skills that they can then provide to potential employers (Section 8/Low income housing, my client is trying to help their residents find steady employment)

The example use case is me selecting GED and HVAC and having a list of all residents that have a GED and HVAC experience.

---

I have a SQL query that obviously doesn't work quite the way I want it, I'm trying to rack my brain thinking of how I can write this so that basically if skill is selected on form = true and resident skill = true then it'll match the query parameters.

How close am I to that goal with the below code?

SELECT *
FROM [Contact Info]
WHERE (Forms!frmSearch!chk_someschool = True AND [Some School] = True)
  AND (Forms!frmSearch!chk_ged  = True AND [GED] = True)
  AND (Forms!frmSearch!chk_highschool  = True AND [High School Diploma] = True)
  AND (Forms!frmSearch!chk_somecollege  = True AND [Some College] = True)
  AND (Forms!frmSearch!chk_certificate  = True AND [Certificate] = True)
  AND (Forms!frmSearch!chk_associates  = True AND [Associate Degree] = True)
  AND (Forms!frmSearch!chk_bachelors  = True AND [Bachelor Degree] = True)
  AND (Forms!frmSearch!chk_masters  = True AND [Masters Degree] = True)
  AND (Forms!frmSearch!chk_accounting  = True AND [Accounting/Bookeeping] = True)
  AND (Forms!frmSearch!chk_admin  = True AND [Administrative/Clerical/Office Training] = True)
  AND (Forms!frmSearch!chk_computer  = True AND [Computer Skills] = True)
  AND (Forms!frmSearch!chk_plumbing  = True AND [Plumbing] = True)
  AND (Forms!frmSearch!chk_concrete  = True AND [Concrete Finishing] = True)
  AND (Forms!frmSearch!chk_groundskeeping  = True AND [Grounds Keeping] = True)
  AND (Forms!frmSearch!chk_HVAC  = True AND [HVAC] = True)
  AND (Forms!frmSearch!chk_casemanagement  = True AND [Case Management] = True)
  AND (Forms!frmSearch!chk_roofer  = True AND [Roofer] = True)
  AND (Forms!frmSearch!chk_carpentry  = True AND [Carpentry] = True)
  AND (Forms!frmSearch!chk_electrical  = True AND [Electrical] = True)
  AND (Forms!frmSearch!chk_bricklayer  = True AND [Brick Layer] = True)
  AND (Forms!frmSearch!chk_receptionist  = True AND [Receptionist] = True)
  AND (Forms!frmSearch!chk_heavyequipment  = True AND [Heavy Equipment Operation] = True)
  AND (Forms!frmSearch!chk_painting  = True AND [Painting] = True)
  AND (Forms!frmSearch!chk_drywall  = True AND [Dry Wall Installation] = True)
  AND (Forms!frmSearch!chk_sheetmetal  = True AND [Sheet Metal Work] = True);

EDIT

@Andre actually helped me with this earlier, with the below, but it only matches residents with EVERY skill/education checked. I tried messing around with the boolean values to see if I get different results and forgot to paste the original statement provided to me.

SELECT *
FROM [Contact Info]
WHERE (Forms!frmSearch!chk_someschool = False OR [Some School] = True)
  AND (Forms!frmSearch!chk_ged  = False OR [GED] = True)
  AND (Forms!frmSearch!chk_highschool  = False OR [High School Diploma] = True)
  AND (Forms!frmSearch!chk_somecollege  = False OR [Some College] = True)
  AND (Forms!frmSearch!chk_certificate  = False OR [Certificate] = True)
  AND (Forms!frmSearch!chk_associates  = False OR [Associate Degree] = True)
  AND (Forms!frmSearch!chk_bachelors  = False OR [Bachelor Degree] = True)
  AND (Forms!frmSearch!chk_masters  = False OR [Masters Degree] = True)
  AND (Forms!frmSearch!chk_accounting  = False OR [Accounting/Bookeeping] = True)
  AND (Forms!frmSearch!chk_admin  = False OR [Administrative/Clerical/Office Training] = True)
  AND (Forms!frmSearch!chk_computer  = False OR [Computer Skills] = True)
  AND (Forms!frmSearch!chk_plumbing  = False OR [Plumbing] = True)
  AND (Forms!frmSearch!chk_concrete  = False OR [Concrete Finishing] = True)
  AND (Forms!frmSearch!chk_groundskeeping  = False OR [Grounds Keeping] = True)
  AND (Forms!frmSearch!chk_HVAC  = False OR [HVAC] = True)
  AND (Forms!frmSearch!chk_casemanagement  = False OR [Case Management] = True)
  AND (Forms!frmSearch!chk_roofer  = False OR [Roofer] = True)
  AND (Forms!frmSearch!chk_carpentry  = False OR [Carpentry] = True)
  AND (Forms!frmSearch!chk_electrical  = False OR [Electrical] = True)
  AND (Forms!frmSearch!chk_bricklayer  = False OR [Brick Layer] = True)
  AND (Forms!frmSearch!chk_receptionist  = False OR [Receptionist] = True)
  AND (Forms!frmSearch!chk_heavyequipment  = False OR [Heavy Equipment Operation] = True)
  AND (Forms!frmSearch!chk_painting  = False OR [Painting] = True)
  AND (Forms!frmSearch!chk_drywall  = False OR [Dry Wall Installation] = True)
  AND (Forms!frmSearch!chk_sheetmetal  = False OR [Sheet Metal Work] = True);

EDIT 2:

Here's the form. Default state false, triple state no. Everything unchecked. Doesn't matter what I have checked though in this form because the result is always the same

When I run it, I only get one result, a dummy entry with everything set check (set to TRUE).

Not sure why though! This is with the query under my first EDIT.

If I try to run my search with just "Associates Degree" checked, I should expect three results. Two actual entries that have an Associates, and my dummy entry, however, only the dummy entry shows up.

Answer 1

As I wrote in my original answer, the logic only works by having OR in the clause for each skill.

Also the check for the search boxes must be for FALSE, not TRUE.

SELECT *
FROM [Contact Info]
WHERE (Forms!frmSearch!chk_someschool = False OR [Some School] = True)
  AND (Forms!frmSearch!chk_ged        = False OR [GED] = True)
  AND (...
                                          ^   ^
                                          |   |

Maybe it helps understanding the logic by reversing it:

Boolean logic says: (A or B) == not (not A and not B)

i.e. you DON'T want to list records, where you DO search for a skill, and the person DOESN'T have the skill

i.e.

  AND NOT (Forms!frmSearch!chk_ged = True AND [GED] = False)

---

Edit: it turned out that you have two checkbox named differently in the query vs. the form.

Form:  chk__concrete
Query: chk_concrete

Form:  chk_carpet
Query: chk_carpentry 

It's kinda annoying that Access doesn't ask for these parameters when running the query. Instead (I guess) it silently takes it as NULL, and these clauses don't work.

Correct these checkbox names in form design, and the query works.