Query or macro for searching multiple yes/no fields

[kwelch]

I have a table with 4 disease conditions. Each disease is a separate field and is set to a yes/no data type.





I used a query and an IIF statement to convert the checkboxes to 1 or null as below.



I want a user (me, really) to be able to choose among the 4 diseases to find the particular patient. So, I might want to see who has allergies AND Asthma in one query, but in the next query find out who has bronchiectasis alone. Alternately, the next query might be who has all four conditions. So, I came up with the following Fields and Criteria:
Field - Allergies: IIf([options]![Allergies]=Yes,1,0)
Criteria - [Allergies: Enter 1 for Yes, 0 for No]

Using this Query method, I can get the answers I want - IF I'm not looking just for a single condition. For example, If I make Allergies a "1" and do not enter anything for the other three diseases (neither a 1 or a 0), I get an empty query. If I enter allergies=1 and the other three diseases as 0, i get only the patients with allergies who do NOT have the other three diseases.

For example, allergies=1, asthma=1, bronchiectasis=1, eosinophilia=0. The resulting query is accurate.


But, that's not what I always want. I may just want to see who has one disease. So, this query method is inadequate for me for two reasons: 1) it takes multiple steps; I'd like a button or something to do the work for me; 2) I cannot query a disease by itself to see how many instances of a single disease there are regardless of whether the other diseases coexist.

I tried working with a split form and creating a macro.


Macro for command button: ApplyFilter, Where Condition = [Allergies] Like "*" & [Forms]![options][CheckAllergies] & "*" ...... etc. This only accepts 255 characters, so it worked for two of the check boxes in the Where Condition. However, when I deselected the 4 disease conditions on the form, the datasheet doesn't "reset" and list all the names again.

So, I'd be interested in hearing what you experts suggest in terms of directions or tweaks.

Thanks!

[ranman256]

You really shouldn't do it that way. Queries won't search across columns, they search vertically.
A sub table to hold infinite ailments.TAilments.
ID, ailment,date
3,bronchitis,1/1/15
3,pneumonia,2/2/16