Handling checkboxes in Access form / sql query

[kashif2005]

Hi, I am new to VBA & access. I am creating a report in Access. In my search form the users can 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] & "*"));

Column 1, 2,3 & 4 are YES/NO fields in my table. And OPTION 1,2,3, & 4 are name of my checkboxes.
The SQL query works perfect if we select/check e.g. one Option only..however if I select Option 1 & 2 both on my search form, the report doesn't display records, although I have records for both the options in my table. If I use OR operator in my SQL query, it displays all records of table, regardless of whether they were CHECKED on the form or not .

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.

 

[alvechurchdata]

If you tick options 1 and 2 then the ANDs won't work because you'll only see those records that have both option 1 and option 2 ticked; the ORs will work too well because you're picking up every records where either option 3 is false or option 4 is false.

OR is what you need but you must remove the unticked fields from the SQL.

I think you need to build the SQL WHERE clause up line by line beforehand rather than trying to embed all the logic into the SQL. If only option 1 is ticked then strSQL should be "WHERE tblMain.[Column1]", if options 1 and 2 are ticked then you need "WHERE tblMain.[Column1] OR tblMain.[Column2]".

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[kashif2005]

Thks Geoff for your suggestion. I have modified my sql statement & it works great. many thanks. Cheers.