Triple State Check Boxs on Form to filter form results

[sheusz]

Hi Minty

Thanks for your input. I'll keep plugging away.

It's one of the reasons that sometimes it is easier to use VBA to build the exact filter/criteria you want rather than making a massive "Query of Doom" covering all the possibilities.

I would think that using VBA would be just as complicated because I would have to check the state of each of the check boxes every time one is clicked, and then refresh the form display.

[sheusz]

Thanks CJ.

That's where I'm getting confused. So, basically every time I introduce a new checkbox, I need to add another criteria to to the each of the existing fields?

[CJ_London]

yes - any combination of 3 checkboxes - 7? possible options. 4 checkboxes - 14? possible options

You can make the query grid simpler by putting brackets around the total criteria e.g.

(tbldrils.cameraunit=[Forms]![frmdrils]![camera] Or [Forms]![frmdrils]![camera] Is Null)

but the query grid will still show it differently to how you wrote it, however probably easier to add additional criteria if you follow the same syntax

[sheusz]

Thanks CJ. I'll give that a try. It's the visualisation of the grid that is throwing me, so this could be a great help.

[CJ_London]

Think of ands and ors as maths functions - you need to be clear what is actually required. In maths

2*12+1=what? (2*12) + 1 =25 or 2*(12+1)=26?

Maths will default to the former - multiplication/division before adding/subtracting, but you can force the alternative by using brackets

With coding you only ever determine a comparison as true or false, there isn't a maybe. Either a value = another value or it doesn't. or it is greater than another value, or it isn't, etc.

So you need to identify your OR's as between brackets (in this case) to separate from the AND'S. You can do it the other way (A=true AND b=true) OR c=true when appropriate

Learning to write sql directly simplifies a lot of things - no repeating of table name when not required, reduction in brackets and square brackets. So for example the sql generated by the query grid looks like this

SELECT tbldrils.ID, tbldrils.drillhead, tbldrils.cameraunit, tbldrils.mistunit, tbldrils.Notes
FROM tbldrils
WHERE (((([tbldrils].[cameraunit])=[Forms]![frmdrils]![camera] Or [Forms]![frmdrils]![camera] Is Null)<>False) AND ((([tbldrils].[mistunit])=[Forms]![frmdrils]![mist] Or [Forms]![frmdrils]![mist] Is Null)<>False) AND ((([drillhead] Is Not Null)=[Forms]![frmdrils]![drill] Or [Forms]![frmdrils]![drill] Is Null)<>False));

It can be simplified to

SELECT ID, drillhead, cameraunit, mistunit, Notes
FROM tbldrils
WHERE (cameraunit=Forms!frmdrils!camera Or Forms!frmdrils!camera Is Null) 
           AND (mistunit=Forms!frmdrils!mist Or Forms!frmdrils!mist Is Null) 
           AND ((drillhead Is Not Null)=Forms!frmdrils!drill Or Forms!frmdrils!drill Is Null)

[sheusz]

Thanks again CJ for your explanation.

I think I'll rework the whole lot writing the SQL directly. It certainly does look a lot easier to follow.

I'll let you know how I end up.

[sheusz]

Hi CJ

I think I have success!

Your comment

Learning to write sql directly simplifies a lot of things - no repeating of table name when not required, reduction in brackets and square brackets.

is certainly true. This is way easier to understand, and easier on the eyes.

SELECT tbldrils.ID, tbldrils.drillhead, tbldrils.cameraunit, tbldrils.mistunit, tbldrils.Notes, tbldrils.airassist, tbldrils.pins

FROM tbldrils

WHERE (cameraunit=Forms!frmdrils!camera Or Forms!frmdrils!camera IsNull) 

AND (mistunit=Forms!frmdrils!mist Or Forms!frmdrils!mist IsNull) 
AND (airassist=Forms!frmdrils!airassist Or Forms!frmdrils!airassist isNull)
AND (pins=Forms!frmdrils!pins Or Forms!frmdrils!pins isNull)
AND ((drillhead IsNotNull)=Forms!frmdrils!drill Or Forms!frmdrils!drill IsNull);

I've attached the DB if you want a look, just in case I've missed something.

Drills-SQL.zip

[CJ_London]

Looks OK to me -

[sheusz]

Hi CJ
Thank you again for your time and patience. This has been a great learning exercise for me. I really appreciate your input and guidance.