Handling checkboxes in Access Report

[kashif73]

Hi, I am creating a report in Access, where the users select checkboxes & they get the required data from table in the report. Below is the sql statement i have written:

SELECT *
FROM tblMain
WHERE (((tblMain.[Column1]) LIKE[forms]![SearchReports]![Option1] & "*") AND ((tblMain.[Column2]) LIKE[forms]![SearchReports]![Option2] & "*") AND ((tblMain.[Column3]) LIKE[forms]![SearchReports]![Option3] & "*") AND ((tblMain.[Column4]) LIKE[forms]![SearchReports]![Option4] & "*"));
 

The SQL query works perfect if we select/check e.g. one Option only..however if I select Option 1 & 2 both, it doesn't display records, whereas I have records for both the options my table. If I use OR operator in my SQL query, it displays all records of table, regardless of whether they were CHECKED or not . any help would be appreciated as I can't see how to configure it. Thks.

[kashif73]

Anyone please? would appreciate suggestions.Thxs.

[ChipR]

What are you trying to compare with the LIKE operator?

[kashif73]

Column 1, 2,3 & 4 are YES/NO fields in my table. And in my sql statement,

((tblMain.[Column1]) LIKE[forms]![SearchReports]![Option1])

Option1, 2,3 & 4 are the names of my checkboxes on the search form. if I check the checkbox for Option 1, then it gives all record from column 1 on my report, like wise if I select OPTION 1 & 2 both on the form, then no record is displayed in the report, coz i am using the AND operator. But If i use OR instead of AND in the sql statement, then if I select OPTION 1 only, all the records related to OPTION 1,2 ,3 & 4 are displayed.

So basically I only want records on my report whose checkboxes are selected by me on the form. I hope i haven't confused you here..thxs for your help.

[ChipR]

Should the records returned match all of the check boxes, or do you want the records who have true values for the options that you checked, regardless of the other fields?

[kashif73]

I only want records who have true values for the options that I checked. E.g. If I select checkbox 1 & 2 (Option 1& 2 on my form), so the REPORT should display all records for these 2 options & ignore OPTION 3 & 4 (since i didn't check them on form).
Thxs.

[ChipR]

In that case, you need to test the values of the check boxes, like:
WHERE
(Column1 = True OR Option1 = False)
AND
(Column2 = True OR Option2 = False)
AND
(Column3 = True OR Option3 = False)
AND
(Column4 = True OR Option4 = False)

Hopefully, that makes sense. If the check box on the form is True, then the value in the column must be true. Otherwise, it doesn't matter. I left out the Form references just to keep it shorter.

[kashif73]

Hi ChipR, can you please write the exact sql statement for me? I haven't understood properly what you are saying here. Thxs

[ChipR]

This depends on the value that you have set your yes/no fields to save in your table. In design view for your table, look at one of the fields and its Format option on the General tab. I have mine set to True/False, so I'll compare the field value to True. Now, it turns out the value of the check box might be Null, so we have to check for that:

SELECT * 
FROM tblMain 
WHERE
([column1] = True OR (Forms!SearchReports!Option1 <> -1) OR Forms!SearchReports!Option1 IS NULL)
AND
([column2] = True OR (Forms!SearchReports!Option2 <> -1) OR Forms!SearchReports!Option2 IS NULL)
AND
([column3] = True OR (Forms!SearchReports!Option3 <> -1) OR Forms!SearchReports!Option3 IS NULL)
AND
([column4] = True OR (Forms!SearchReports!Option4 <> -1) OR Forms!SearchReports!Option4 IS NULL)

Basically, the condition is: If the check box is null, that field doesn't have to be true, OR if the check box is false (-1), that field doesn't have to be true. OR the field has to be true.

[kashif73]

Thxs ChipR..it works great..really appreciate your help.
Cheers.