Query criteria base on checkbox (1 Viewer)

[xdenama]

I'm trying to run the following as criteria in an MS access query. Basically what I want to do is:

• If checkbox (Check0) = True and (Check2) = False, then only records that match the value "HighSc" in Field "ScType" are shown. or otherwise value "Town" in Field "Location" are shown.
• If checkbox (Check0) and (Check2) = True then records that match the value "HighSc" in Field "ScType" and value "Town" in Field "Location" are shown. All match value from both field should be shown.
• If all checkbox = False then all records from all Field are shown.

 

[GinaWhipp]

<Did not look at database>

You really need to consider a Select Case statement with a command button on the Form. I just can't see a way to make all that work in a query.

 

[sneuberg]

I believe you can do want you want with a iif in the criteria of the query. In the attached database I modified query1 so that the criteria for Locations is:

Like IIf([forms]![Form1]![Check0]=True,"Town","*")

And for the Grade :

Like IIf([forms]![Form1]![Check2]=True,"HighSc","*")

I added a report base on the query for testing. This report is run from a button on the form. Please let us know if this is what you want and works for you.

 

[arnelgp]

you must Refresh the query to retrieve new records.

 

[xdenama]

I believe you can do want you want with a iif in the criteria of the query. In the attached database I modified query1 so that the criteria for Locations is:

Like IIf([forms]![Form1]![Check0]=True,"Town","*")

And for the Grade :

Like IIf([forms]![Form1]![Check2]=True,"HighSc","*")

I added a report base on the query for testing. This report is run from a button on the form. Please let us know if this is what you want and works for you.

you must Refresh the query to retrieve new records.

Thanks for reply. I have done the same thing before. But it does not give the desired answer. When both checkbox is checked, only overlap value are shown (disired answer is, all data that match both value are shown). And when both checkbox unchecked, not all value from "tblSourceB" shown, only value from "ScProfil" that are not empty shown.

 

[arnelgp]

i think the query i posted is your requirement in post 1.

 

[xdenama]

i think the query i posted is your requirement in post 1.

Yes Sir, in case (If all checkbox = False then all records from all Field are shown.) is working.

But in case (If checkbox (Check0) and (Check2) = True then records that match the value "HighSc" in Field "ScType" and value "Town" in Field "Location" are shown. All match value from both field should be shown) not working.

 

[arnelgp]

after clicking on any of the combo, make sure to refresh your query (Press Query All from ribbon).

also there is no ScType field, only Grade field.

 

[xdenama]

after clicking on any of the combo, make sure to refresh your query (Press Query All from ribbon).

also there is no ScType field, only Grade field.

Sorry for making you a bit of confusing. As a matter of fact, i actually made a mistake by typing the "Grade" Field instead of "ScType " Field. Hence, I have made the correction below (the red text). Besides that, when both the checkbox are checked, it only appears two data which is overlapping. I hope that all the data which are overlapped and do not overlapped appear when both checkbox are checked. The total data should appear 7 instead of 2.

• If checkbox (Check0) and (Check2) = True then records that match the value "HighSc" in Field "Grade" and value "Town" in Field "Location" are shown. All match value from both field should be shown.

Apart from that, i did overlooked my previous statement (point 3). So, i have corrected my statement as shown below (the red text). Previously, you have made all my data stated in ScProfil and it is working sistematically. Unfortunately, i wish all the values from table "tblSourceB" will be shown not only table "ScProfil".

• If all checkbox = False then all records from all Field in table "tblSourceB" are shown.

 

[sneuberg]

One way to do this is create an expression that is true for the conditions you specify. With that expression as a field the criteria for the expression is simply "True". The raw expression for that is:

LogicTest: ([Forms]![Form1]![Check0] And [Forms]![Form1]![Check2] And ([Location]="Town" Or [Grade]="HighSc")) Or ([Forms]![Form1]![Check0] And Not [Forms]![Form1]![Check2] And [Location]="Town") Or (Not [Forms]![Form1]![Check0] And [Forms]![Form1]![Check2] And [Grade]="HighSc") Or (Not [Forms]![Form1]![Check0] And Not [Forms]![Form1]![Check2])

which has been added to query1 in the attached database. I believe this produces the results you want.

@all you boolean algebra experts out there: Try and simplify this.

Note this is not easily extensible. I'll keep on thinking about this with a mind toward something simplier.

 

[sneuberg]

With the help of http://www.32x8.com/ I was able to greatly simplify the logic I came up with in my previous post. It is now

LogicTest: [Forms]![Form1]![Check0] And [Location]="Town" Or [Forms]![Form1]![Check2] And [Grade]="HighSc" Or Not [Forms]![Form1]![Check0] And Not [Forms]![Form1]![Check2]

and while this looks complicated it is really not. Basically the check boxes are used as switches to turn on a condition. So for example check0 which controls the location is anded with that condition; namely [Location]="Town". The individual check box anded with their conditions are all ored together with one additional condition of all the check boxes being off.

I also changed the defaults of the Check0 and Check@ check boxes to false in this version.

 

[xdenama]

LogicTest: [Forms]![Form1]![Check0] And [Location]="Town" Or [Forms]![Form1]![Check2] And [Grade]="HighSc" Or Not [Forms]![Form1]![Check0] And Not [Forms]![Form1]![Check2]

and while this looks complicated it is really not. Basically the check boxes are used as switches to turn on a condition. So for example check0 which controls the location is anded with that condition; namely [Location]="Town". The individual check box anded with their conditions are all ored together with one additional condition of all the check boxes being off.

I also changed the defaults of the Check0 and Check@ check boxes to false in this version.

Dear Sneuberg,thank you for your help. I really2 appreciate your great effort in solving my problem. I will try my best in working the real database with another 8 checkbox at least. I will let you know my update.