Making Multiple Customized Selections With Checkboxes

[Tiff]

Hello All,

I have created a historical database for my military command using Access. It allows users to select certain parameters to customize the search to find exactly what they what. For example, they can search for a unit by name and year, they can search by just year, year and quarter, and so forth. These functions were easy for me to configure. The problem I am running into is when using the check boxes. Here is a picture to help show what I am trying to accomplish. The first picture shows the table that I use for my query. As you can see, there are checkboxes next to each items. My command deals with ships, subs, shore units, and one other type. The next photo is the form I create as the user interface. The checkboxes correspond to the ones in the table. If I check the first checkbox, all the items that are checked will show up. If I check the first box and the second box, nothing will show UNLESS there is an item that has both checked in the table. What I would like is to be able to check any combination of the boxes and have all items that have any combination of the boxes checked come back in the query results. I am very new to Access so it may just be something simple I am overlooking. Any help would be greatly appreciated. I have also included the code below. I am only concerned with the Afloat Unit, Proof Of Concept, and Sub Unit. Everything else is working as I would like. THANK YOU!!!!

SELECT DISTINCTROW [tbl_Unit TAV Information Table_1].UNIT, [tbl_Unit TAV Information Table_1].[TAV START], [tbl_Unit TAV Information Table_1].[TAV END], [tbl_Unit TAV Information Table_1].[FISCAL YEAR], [tbl_Unit TAV Information Table_1].QUARTER, [tbl_Unit TAV Information Table_1].[STAGE III DATE], [tbl_Unit TAV Information Table_1].LOCATION, [tbl_Unit TAV Information Table_1].SCORE
FROM [tbl_Unit TAV Information Table], [tbl_Unit TAV Information Table] AS [tbl_Unit TAV Information Table_1]


WHERE ((([tbl_Unit TAV Information Table_1].UNIT) Like "*" & ([forms]![frm_UnitReports]![UnitSearch_Textbox]) & "*") AND (([tbl_Unit TAV Information Table_1].[FISCAL YEAR]) Like "*" & ([forms]![frm_UnitReports]![FiscalYearSearch]) & "*") AND (([tbl_Unit TAV Information Table_1].QUARTER) Like "*" & ([Forms]![frm_UnitReports]![Quarter_Selection]) & "*") AND (([tbl_Unit TAV Information Table_1].[AFLOAT UNIT])=[forms]![frm_UnitReports]![Afloat_Checkbox]) AND (([tbl_Unit TAV Information Table_1].[Proof of concept])=[forms]![frm_UnitReports]![ProofOfConcept_Checkbox]) AND (([tbl_Unit TAV Information Table_1].[SUB UNIT])=[forms]![frm_UnitReports]![SubUnit_Checkbox]))
ORDER BY [tbl_Unit TAV Information Table_1].[TAV START] DESC , [tbl_Unit TAV Information Table_1].[TAV END] DESC , [tbl_Unit TAV Information Table_1].[FISCAL YEAR] DESC , [tbl_Unit TAV Information Table_1].QUARTER DESC;

[Perceptus]

Im horrible with blocks of text. BUT i think this is what I see.

you are using AND statements in your SQL statement. You need nested And with or in the nest to match for the multiple possibilities and not just the one.

(([tbl_Unit TAV Information Table_1].[AFLOAT UNIT])=[forms]![frm_UnitReports]![Afloat_Checkbox]) AND (([tbl_Unit TAV Information Table_1].[Proof of concept])=[forms]![frm_UnitReports]![ProofOfConcept_Checkbox]) AND (([tbl_Unit TAV Information Table_1].[SUB UNIT])=[forms]![frm_UnitReports]![SubUnit_Checkbox]))

Should use OR not AND

[orange]

I agree with Perceptus---- OR will give records with any of the values, AND requires them all to be present.
Also, I notice that some of the UNITs are repeated/duplicated in the picture -- is that intentional?

[Tiff]

Hi,
Thank for your response. Having the same units multiple times is intentional. We are tracking how many times we have done assessment for that particular unit. So if they show more than once, they have been assessed multiple times.

-Tiffany