Triple State Check Boxs on Form to filter form results

[sheusz]

Hello Brains Trust



With the help of the users on this forum I have learnt more than I could have done on my own about using and coding Access, however I am stumped by this problem that I have been working on for some time.

I have a form that is used to display search results based on some triple state check boxes on the form.

The form is based on a query containing the required fields to display on the form.

On opening, the form displays all unfiltered results of the query. The user then uses the check boxes to filter out data that they do or do not want to see.

It all works fine on fields that are based on text boxes on the underlying table. However I've been asked to add a text field to the search form so that users can add that to their filtered search results.

I created an expression in the query that gives me a NO if the field in the table is empty, or a YES, if the field in the table has a value, thinking that I could use a triple state check box on the calculated field. This is where I am having my issue.

DrillTrue: IIf(IsNull([DrillHead]),"No","Yes")

I want the triple state checkbox called Drill on the form to work on the expression DrillTrue, to show all values that are YES if checked, all values that are NO if unchecked otherwise show all results if the checkbox is greyed out (no value).


This is the SQL code for the query.

SELECT Customers.CompanyName, [CustomerMachineComponents].MachineNo, [xMachineModel].[MachineModel], [CustomerMachineComponents].[PurchDate], [CustomerMachineComponents].[AirAssistHead], [CustomerMachineComponents].[Pop-UpPins], [CustomerMachineComponents].[MaterialOnLoader], [CustomerMachineComponents].[MaterialOffLoader], [CustomerMachineComponents].[MaterialLifter], [CustomerMachineComponents].MaterialLifter, [CustomerMachineComponents].[ICSCamera], [CustomerMachineComponents].Mister, [CustomerMachineComponents].MisterType, [CustomerMachineComponents].[DrillHead], IIf(IsNull([DrillHead]),"No","Yes") AS DrillTrue
FROM [xMachineModel] INNER JOIN (Customers INNER JOIN [CustomerMachineComponents] ON Customers.CustomerID = [CustomerMachineComponents].CustomerID) ON [xMachineModel].ModelID = [CustomerMachineComponents].ModelID
WHERE ((([CustomerMachineComponents].[AirAssistHead])=IIf(IsNull([Forms]![frmAdvancedSearch]![AirAssist]),[AirAssist Head],[Forms]![frmAdvancedSearch]![AirAssist])) AND (([CustomerMachineComponents].[Pop-UpPins])=IIf(IsNull([Forms]![frmAdvancedSearch]![PopUpPins]),[Pop-Up Pins],[Forms]![frmAdvancedSearch]![PopUpPins])) AND (([CustomerMachineComponents].[MaterialOnLoader])=IIf(IsNull([Forms]![frmAdvancedSearch]![OnLoader]),[MaterialOnLoader],[Forms]![frmAdvancedSearch]![OnLoader])) AND (([CustomerMachineComponents].[MaterialOffLoader])=IIf(IsNull([Forms]![frmAdvancedSearch]![OffLoader]),[MaterialOffLoader],[Forms]![frmAdvancedSearch]![OffLoader])) AND (([CustomerMachineComponents].[MaterialLifter])=IIf(IsNull([Forms]![frmAdvancedSearch]![Lifter]),[MaterialLifter],[Forms]![frmAdvancedSearch]![Lifter])) AND (([CustomerMachineComponents].[ICSCamera])=IIf(IsNull([Forms]![frmAdvancedSearch]![Camera]),[ICSCamera],[Forms]![frmAdvancedSearch]![Camera])) AND (([CustomerMachineComponents].Mister)=IIf(IsNull([Forms]![frmAdvancedSearch]![Mist]),[Mister],[Forms]![frmAdvancedSearch]![Mist])))
ORDER BY Customers.CompanyName;

I am hoping that someone may be able to offer a solution or point me in the right direction.

Thanking you in advance for your time.

[CJ_London]

you can only apply triple state to unbound checkboxes where the controlsource is an integer (populated with -1, 0 or null). At the moment you are populating DrillTrue with text which is not numeric and only has two options.


I would change your drillTrue to return a boolean value

DrillTrue: [DrillHead] is not null

and your Drill control remains unbound (or set a default to true or false)

then your filter becomes

DrillTrue=Drill OR Drill is null

or perhaps

DrillTrue=Drill OR isnull(Drill)

(not sure if is null works on a filter as it is sql syntax)

[sheusz]

Hi CJ

Thanks for your suggestion. I sort of understand and will have a go. This is all a bit new to me.

[sheusz]

Hi CJ

I'm making some progress...

The control, Drill, is unbound and the default is set to null

In the query the expression is

DrillTrue: [DrillHead] is not null.

The query criteria for DrillHead is currently blank (for testing) so it isn't checking the state of the Drill control on the form.

When I run the query it shows all records with a -1 if there is no DrillHead and a 0 if there is a DrillHead. So far so good.

Now, when I introduce the criteria in to the DrillHead field on the query and run it, without entering T or F in to the parameter entry dialog box, it only shows the records where DrillHead is true, where it should be showing all records in the table.

IIf(IsNull([Forms]![frmAdvancedSearch]![Drill]),[DrillHead],[Forms]![frmAdvancedSearch]![Drill])

If I enter T or F in to the parameter entry dialog box, it shows no results.

Frankly I'm stumped.

[CJ_London]

show your query sql

[CJ_London]

this is a simple example of what I am suggesting

[sheusz]

Hi CJ

Apologies for the late response. Been away.

Thank you very much for the sample DB. It has helped enormously.

The only change I made was to change Drillhead to Is Not Null so that the check box works the way I want it to.

For clarity.

If a check box is Null, then show every record.
If a check box is ticked, then show only those records
If a checkbox, then don't show these records.

The only issue now is that, with your suggestions added to the query, none of the other check boxes on the form work if the drills check box is Null. ie, the for display is not updated.

If it is ticked or unticked, then the other check boxes will work correctly and update the results on the form. I've attached a copy of the DB to demonstrate the issue.

Drills2.zip

Thank you again for your assistance.

SELECT Customers.CompanyName, [CustomerMachineComponents].MachineNo, [xMachineModel].[MachineModel], [CustomerMachineComponents].[PurchDate], [CustomerMachineComponents].[AirAssistHead], _
[CustomerMachineComponents].[Pop-UpPins], [CustomerMachineComponents].[MaterialOnLoader], [CustomerMachineComponents].[MaterialOffLoader], [CustomerMachineComponents].[MaterialLifter], _
[CustomerMachineComponents].MaterialLifter, [CustomerMachineComponents].[ICSCamera], [CustomerMachineComponents].Mister, [CustomerMachineComponents].MisterType, [CustomerMachineComponents].[DrillHead]
FROM [xMachineModel] INNER JOIN (Customers INNER JOIN [CustomerMachineComponents] ON Customers.CustomerID = [CustomerMachineComponents].CustomerID) ON [xMachineModel].ModelID = [CustomerMachineComponents].ModelID
WHERE ((([CustomerMachineComponents].[AirAssistHead])=IIf(IsNull([Forms]![frmAdvancedSearch]![AirAssist]),[AirAssistHead],[Forms]![frmAdvancedSearch]![AirAssist])) _
AND (([CustomerMachineComponents].[Pop-UpPins])=IIf(IsNull([Forms]![frmAdvancedSearch]![PopUpPins]),[Pop-UpPins],[Forms]![frmAdvancedSearch]![PopUpPins])) _
AND (([CustomerMachineComponents].[MaterialOnLoader])=IIf(IsNull([Forms]![frmAdvancedSearch]![OnLoader]),[MaterialOnLoader],[Forms]![frmAdvancedSearch]![OnLoader])) _
AND (([CustomerMachineComponents].[MaterialOffLoader])=IIf(IsNull([Forms]![frmAdvancedSearch]![OffLoader]),[MaterialOffLoader],[Forms]![frmAdvancedSearch]![OffLoader])) _
AND (([CustomerMachineComponents].[MaterialLifter])=IIf(IsNull([Forms]![frmAdvancedSearch]![Lifter]),[MaterialLifter],[Forms]![frmAdvancedSearch]![Lifter])) _
AND (([CustomerMachineComponents].[ICS Camera])=IIf(IsNull([Forms]![frmAdvancedSearch]![Camera]),[ICSCamera],[Forms]![frmAdvancedSearch]![Camera])) _
AND (([CustomerMachineComponents].Mister)=IIf(IsNull([Forms]![frmAdvancedSearch]![Mist]),[Mister],[Forms]![frmAdvancedSearch]![Mist])) _
AND (([DrillHead] Is Not Null)=[Forms]![frmAdvancedSearch]![Drill])) OR ((([Forms]![frmAdvancedSearch]![Drill]) Is Null))
ORDER BY Customers.CompanyName;

[CJ_London]

you need to apply the same principle to the other checkboxes


e.g. [Forms]![frmdrils]![camera] OR [Forms]![frmdrils]![camera] is null


when you have completed for all three, and saved the query, you will find Access has reorganised the query grid to account for the mix of AND's and OR's

With regards your copy query - check what you have actually specified as a criteria. use of IIf and Isnull functions may work for you, but use it across all criteria, not just two.

See attached
Drills.zip

[sheusz]

Hi CJ

Thank you for this. I'm going to have to spend some time figuring out how this works.

Quick question. I've noticed that when I enter an expression in a field it is usually assigned a value of Expr1: etc

SELECT tbldrils.ID, tbldrils.drillhead, tbldrils.cameraunit, tbldrils.mistunit, tbldrils.Notes, [drillhead] IsNotNullAS Expr1
FROM tbldrils
WHERE (((tbldrils.cameraunit)=[Forms]![frmdrils]![camera]) AND ((tbldrils.mistunit)=[Forms]![frmdrils]![mist]) AND (([drillhead] IsNotNull)=[Forms]![frmdrils]![drill])) OR (((tbldrils.mistunit)=[Forms]![frmdrils]![mist]) AND (([drillhead] IsNotNull)=[Forms]![frmdrils]![drill]) AND (([Forms]![frmdrils]![camera]) IsNull)) OR (((tbldrils.cameraunit)=[Forms]![frmdrils]![camera]) AND (([drillhead] IsNotNull)=[Forms]![frmdrils]![drill]) AND (([Forms]![frmdrils]![mist]) IsNull)) OR ((([drillhead] IsNotNull)=[Forms]![frmdrils]![drill]) AND (([Forms]![frmdrils]![camera]) IsNull) AND (([Forms]![frmdrils]![mist]) IsNull)) OR (((tbldrils.cameraunit)=[Forms]![frmdrils]![camera]) AND ((tbldrils.mistunit)=[Forms]![frmdrils]![mist]) AND (([Forms]![frmdrils]![drill]) IsNull)) OR (((tbldrils.mistunit)=[Forms]![frmdrils]![mist]) AND (([Forms]![frmdrils]![camera]) IsNull) AND (([Forms]![frmdrils]![drill]) IsNull)) OR (((tbldrils.cameraunit)=[Forms]![frmdrils]![camera]) AND (([Forms]![frmdrils]![mist]) IsNull) AND (([Forms]![frmdrils]![drill]) IsNull)) OR ((([Forms]![frmdrils]![camera]) IsNull) AND (([Forms]![frmdrils]![mist]) IsNull) AND (([Forms]![frmdrils]![drill]) IsNull));

and sometimes the Expr1: isn't there

SELECT tbldrils.ID, tbldrils.drillhead, tbldrils.cameraunit, tbldrils.mistunit
FROM tbldrils
WHERE (((tbldrils.cameraunit)=IIf(IsNull([Forms]![frmdrils]![camera]),[mistunit],[Forms]![frmdrils]![camera])) AND ((tbldrils.mistunit)=IIf(IsNull([Forms]![frmdrils]![mist]),[mistunit],[Forms]![frmdrils]![mist])) AND (([drillhead] IsNotNull)=[Forms]![frmdrils]![drill])) OR ((([Forms]![frmdrils]![drill]) IsNull));

Both work, but I'm curious as to why this is so. Never seen this behavior before.

[CJ_London]

If you are not showing the value, you don’t need to give it a name. When you save a query it does a number of tidying up exercises such as this - will also move these criteria to the end

[Micron]

To that I'll add [Exp1] is a default field name. Next would be [Exp2] and so on - unless you give it your own name. That would usually be far more useful on a form or report recordsource than using the default. Depending on how you build your form/report you could end up with a control and/or label with the default names which you would then need to edit to make its meaning clear. Might as well do that at the beginning.

Open the query in sql view and read what you see. The As keyword denotes what field name contains the expression. Do you really see what you posted? I have to think that
IsNotNullAs Exp1 is a post typo.

[sheusz]

Hi CJ

OK. Thanks. It's a bit off putting.

In studying the query, I'm still struggling to grasp how it works.

For example, why do each of the query fields have 4 criteria and is the positioning of the criteria for each field important?



If I want to add another field, does the number of criteria need to be increased (from 4 to 5) to allow for the extra field?

Sorry about the questions, but I really do want to try and understand how this is working.

[sheusz]

Hi Micron

Thanks for your input. I generally do name my expressions, however access added this one itself, and I'm not using this expression on the form.

You are correct sNotNullAs Exp1 is a typo. Not sure how that happened because I copied and pasted it from the Db.

[Minty]

If you look at the SQL code generated for that query, you will find Access has added approx. 1.2 million parentheses to the where clause.

It's just what it does, to sort out the complicated number of variables, with the Or MyControl Is Null sections.
In order to display it correctly in the visual Query designer, it has to format in the way it shows you.

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.

[CJ_London]

, why do each of the query fields have 4 criteria and is the positioning of the criteria for each field important?

you have many optional conditions

a and b and c
a or b and c
a and b or c

etc