Solved Filtering a query based on a checkbox in a different form (1 Viewer)

[zebrafoot]

Hello.

I'm trying to design a search form whereby a user can search through all the contacts in our customer database using a number of criteria. The idea is that the user can enter partial data, like surname (or part of surname), organisation name etc. I'd also like to include a checkbox to search for whether the contact is active or not.

On clicking a button, another form is opened that should display all the contacts filtered by the appropriate criteria. All well and good except that the "Active" checkbox is not returning the expected results - if "Active" is ticked, I get 570 contacts returned (all the contacts in the database). If unticked, I get 538 back. I've tested the syntax of my criterion in a test query and it returns the correct numbers. This is it:

[Forms]![frmContactSearch].[form]![chkContactActive].[value]

So as you can see, it's referencing a form called "frmContactSearch" and filtering based on the status of the checkbox "chkContactActive". To reiterate, this works fine in a very simple query, but falls over in the more complex one. I can't share my data for security reasons, but is there something I should be looking for in the way I've constructed the query underlying my form that might cause this sort of behaviour?

Many thanks,
Pete

P.S. sorry posted early by accident, before I'd finished writing.

 

[plog]

To reiterate, this works fine in a very simple query, but falls over in the more complex one.

You totally lost me with that. Right before that statement you said the criteria works in a test query--so does it or does it not work in your test query?

Then some random ideas:

How are you filtering your form? What specific line of code are you using on it?

What type of field is the Active field? Yes/No, Text, number?

 

[The_Doc_Man]

[Forms]![frmContactSearch].[form]![chkContactActive].[value]

You could try simplifying that: Forms("frmContactSearch")![chkContactActive] is all you need. The ".[form]" is extraneous. The ".[value]" is the default for any control that actually HAS a value. Of course, it is imperative that the named form must actually be open at the time.

As to your query behavioral issues, it would help us to see the text of the query even if we can't see sample data. However, plog's question about data type for the Active field is also useful. To say that you have the string as a criterion is nice, but in what larger context? (That's why we need to see the query.)

 

[zebrafoot]

Hi Plog,

I have a form with lots of unbound controls in it, for things like:

Name
Organisation
Department
etc

I want the user to be able to enter some text into the controls (partial names etc).

When the search results are displayed they are filtered using criteria like this:
Like "*" & [Forms]![frmContactSearch].[Form]![txtName] & "*"

which will only return contacts with a name that contains the value in the unbound control from the first form. This bit works fine. However, if I wish to filter between active and inactive contacts (a yes/no value) I can't get that criterion to work. I was concerned that I was entering the criterion incorrectly, so I built a small test query with only ContactID, Contact surname and active (yes/no) and tested it using the criterion I mentioned above ([Forms]![frmContactSearch].[form]![chkContactActive].[value]). That test query worked ok, but the one underlying my form didn't - I don't understand why not.

 

[zebrafoot]

Hi The_Doc_Man,

Here is the SQL of the query:

SELECT tblContacts.ContactID, tblContacts.ContactActive, tblContacts.ContactReportsToID, tblContacts.DepartmentID, tblContacts.ContactSurname, tblContacts.ContactForename, tblContacts.ContactTitleID, tblBuilding.BuildingID, tblBuilding.BuildingName, tblBuilding.BuildingAddress1, tblBuilding.BuildingAddress2, tblBuilding.BuildingAddress3, tblBuilding.BuildingPostCode, tblContacts.BuildingID, tblContacts.ContactNotes, tblContacts.ContactEmail, tblContacts.ContactTelephone1, tblContacts.ContactTelephone2, tblDepartment.DepartmentName, tblOrganisation.OrganisationName, tblOrganisation.OrganisationID, tblDepartment.DepartmentID, tblFinanceOffice.FinanceEmailAddress, tblFinanceOffice.FinanceOfficeID, tblFinanceOffice.FinanceOfficeName, tblFinanceOffice.FinanceAddress1, tblFinanceOffice.FinanceAddress2, tblFinanceOffice.FinanceAddress3, tblFinanceOffice.FinanceAddress4, tblFinanceOffice.FinanceCity, tblFinanceOffice.FinancePreferredInvoiceID, tblContacts.ContactGroupOrBuildingLocation, tblBuilding.BuildingCity, tblContacts.ContactLastModified, tblContacts.ContactModifiedBy, tblContacts.ContactPhoto, tblContactTitle.ContactTitle, tblContactJobDescription.ContactJobDescription, tblContacts.ContactDateCreated, tblContacts.ContactActive
FROM tblOrganisation INNER JOIN (tblFinanceOffice RIGHT JOIN (tblDepartment INNER JOIN (tblContactTitle INNER JOIN (tblContactJobDescription RIGHT JOIN (tblBuilding INNER JOIN tblContacts ON tblBuilding.BuildingID = tblContacts.BuildingID) ON tblContactJobDescription.ContactJobDescriptionID = tblContacts.ContactJobDescriptionID) ON tblContactTitle.ContactTitleID = tblContacts.ContactTitleID) ON tblDepartment.DepartmentID = tblContacts.DepartmentID) ON tblFinanceOffice.FinanceOfficeID = tblDepartment.FinanceOfficeID) ON tblOrganisation.OrganisationID = tblDepartment.OrganisationID
WHERE (((tblContacts.ContactID) Like "*" & [Forms]![frmContactSearch].[Form]![txtContactID] & "*") AND ((tblContacts.ContactSurname) Like "*" & [Forms]![frmContactSearch].[form]![txtContactSurname] & "*") AND ((tblContacts.ContactForename) Like "*" & [Forms]![frmContactSearch].[form]![txtContactForename] & "*") AND ((tblBuilding.BuildingName) Like "*" & [Forms]![frmContactSearch].[Form]![txtBuildingName] & "*") AND ((tblOrganisation.OrganisationName) Like "*" & [Forms]![frmContactSearch].[form]![txtOrganisationName] & "*") AND ((tblContacts.ContactGroupOrBuildingLocation) Like "*" & [Forms]![frmContactSearch].[form]![txtLocationGroup] & "*") AND ((tblContactJobDescription.ContactJobDescription) Like "*" & [Forms]![frmContactSearch].[form]![txtContactJobDescription] & "*") AND ((tblContacts.ContactDateCreated) Between (Nz(([Forms]![frmContactSearch].[Form]![txtAfter]),1/1/1901)) And (Nz([Forms]![frmContactSearch].[Form]![txtBefore],#1/1/3000#))) AND ((tblContacts.ContactActive)=[Forms]![frmContactSearch].[form]![chkContactActive].[value])) OR ((([Forms]![frmContactSearch].[Form]![txtLocationGroup]) Is Null))
ORDER BY tblContacts.ContactID DESC;


The above doesn't filter properly on whether the box chkContactActive is clicked. The much simpler one below does:

SELECT tblContacts.ContactID, tblContacts.ContactSurname, tblContacts.ContactActive
FROM tblContacts
WHERE (((tblContacts.ContactActive)=[Forms]![frmContactSearch].[form]![chkContactActive].[value]));

 

[plog]

When you JOIN tables the results contain Table A records times matching Table B records.

TableA
FieldA1, FieldA2
W, A1
W, A2
W, A3
X, A1
X, A2
Z, A1

TableB
FieldB1, FieldB2
W, B1
W, B2
X, B1
Y, B1
Z, B1

SELECT FieldA1, FieldA2, FieldB1, FieldB2 FROM TableA INNER JOIN TableB ON FieldB1 = FieldA1

The query will have 6 W records (3 in A * 2 in B), 2 X records (2 in A * 1 in B), 0 Y records (0 in A * 1 in B), 1 Z records (1 in A * 1 in B). That's because Every W record in A will match with every W record in B like so:

W, A1, W, B1
W, A1, W, B2
W, A2, W, B1
W, A2, W, B2
W, A3, W, B1
W, A3, W, B2

This is what is happening with your complex query. I don't know the solution for you, but it probably involves eliminating a table in the JOIN to eliminate so many matches. Throw out any data you don't really need in the underlying data for your form, don't use every table if you are not going to use every table.

 

[The_Doc_Man]

I am focusing on the parenthetical groupings in your WHERE clause. When I dropped in a number in Bold it is because I am matching depth of parenthetical expressions to see what matches up. Because code tags suppress bold tags, I can't show this in code tags.

WHERE
(1 (2 (3 tblContacts.ContactID 2) Like "*" & [Forms]![frmContactSearch].[Form]![txtContactID] & "*" 1)
AND (2 (3 tblContacts.ContactSurname 2) Like "*" & [Forms]![frmContactSearch].[form]![txtContactSurname] & "*" 1)
AND (2 (3 tblContacts.ContactForename 2) Like "*" & [Forms]![frmContactSearch].[form]![txtContactForename] & "*" 1)
AND (2 (3 tblBuilding.BuildingName 2) Like "*" & [Forms]![frmContactSearch].[Form]![txtBuildingName] & "*" 1)
AND (2 (3 tblOrganisation.OrganisationName 2) Like "*" & [Forms]![frmContactSearch].[form]![txtOrganisationName] & "*" 1)
AND (2 (3 tblContacts.ContactGroupOrBuildingLocation 2) Like "*" & [Forms]![frmContactSearch].[form]![txtLocationGroup] & "*" 1)
AND (2 (3 tblContactJobDescription.ContactJobDescription 2) Like "*" & [Forms]![frmContactSearch].[form]![txtContactJobDescription] & "*" 1)
AND (2 (3 tblContacts.ContactDateCreated 2) Between
(3 Nz(4(5 [Forms]![frmContactSearch].[Form]![txtAfter] 4),1/1/1901 3) 2) And
(3 Nz(4 [Forms]![frmContactSearch].[Form]![txtBefore],#1/1/3000# 3) 2) 1)
AND (2 (3 tblContacts.ContactActive 2)=[Forms]![frmContactSearch].[form]![chkContactActive].[value] 1) 0)
OR (1 (2 (3 [Forms]![frmContactSearch].[Form]![txtLocationGroup] 2) Is Null 1) 0)

Notice the 0 cases and the OR that is between them. That expression says "Match - via a bunch of LIKE expressions - EACH of seven text-oriented fields and a between-time range and exactly match a Y/N field ... OR if the location group is null, bring in everything." In essence, it is EITHER matching on that lump of LIKE comparisons, or if your location group is null, it ignores every field and gives you all of the records back. ALL of them.

I don't know if that's what you wanted, but that's what you've got. IF that isn't what you wanted, your problem is parenthetical mismatching. Since I'm not sure what you are doing entirely, you will have to decide what you wanted. But if I may offer a bit of advice, give us a more careful verbal description of your goal. Avoid using code as an explanation. That is a LOT of AND connectors when you might well have meant OR connectors. But again, I can't tell with certainty 'cause I don't know your goal.

 

[zebrafoot]

Hi both,

Many thanks for your paitience. I'm trying to explain this in very simple terms, so I'm sorry if my goal is not clear Doc Man. My boss is a big fan of Filemaker, and he wants to be able to search all fields within a record in the same way as it is possible in Fm. So for example, I would like to search customers with a forename beginning with "Fr" and surname beginning with "Blo" in an organisation containing "Cam" - this might return Fred Bloggs from Cambridge. I'm filtering the results using criteria within the query that reference the content of controls on another form - Like "*" & [forms]![frmContactSearch].[form]![txtOrganisationName] & "*", for example. This bit seems to work fine, as long as I remember that some of the fields are null and include an OR criterion [forms]![frmContactSearch].[form]![txtSomeField] Is Null.

I hope that bit above is clear enough.

I've got my query working correctly for some search fields - so for example if I put "pr" in the title search and "sm" in the surname search, I can return Professor Smith.

What I CAN'T figure out is how to filter out all the INACTIVE contacts. If I construct a simple test query using ContactID, ContactSurname and ContactActive I can successfully filter my results using the criterion [Forms]![frmContactSearch].[form]![chkContactActive].[value]. The query reads the value of ContactActive on the open search form and returns active or inactive contacts accordingly. This method DOES NOT work in my more complex query (that brings in department, address etc) and I have no idea why.

 

[zebrafoot]

After a bit more playing, I think what you say about the Nulls is right Doc Man. Here's a worked example:

If I use Like "*" & [forms]![frmContactSearch].[Form]![txtContactForename] & "*" and [forms]![frmContactSearch].[Form]![chkContactActive], I can filter on part of the forename and the Active/Inactive.

If I now add a similar criterion for surname I can filter for that too. The problem I'm not retrieving all my records if there is a null value for one of the fields - for example where there is a forename.

Let's imagine I have a set of customers:

Forename Surname
NULL Bloggs
Fred Smith
Jane NULL

If I don't filter on ANY names I'd like to return 3 records. If I filter on forename containing "e" I'd like to return Fred and Jane. If I filter on surname containing "gg" I'd like to return Bloggs. I just don't understand how to add criteria to a query to achieve this. Obviously it would be easier to not have null values in the database for things like names, but the data is historical and that's the way my boss used to do things.

I think the active/inactive checkbox thing is a red herring - as you suggested Doc Man, I believe I was just returning all records because of some other change I'd made to the query.

Pete

 

[zebrafoot]

...one step further ahead on this.

If I modify the criterion to this:

[forms]![frmContactSearch].[Form]![txtContactForename] OR Is Null

I can at least return the records with a null value for forename when searching for somebody by surname. However, if I search for a forename, "Pete" for example, I get back the "Petes" and "Peters", but also the records where forename is null. What I want is to return all the records with null forename when searching for surname, but NOT return null forenames when searching with a specific search string in the forename field.

Pete

 

[zebrafoot]

First up, thanks to plog and Doc Man for helping with this. I really appreciate your time and effort.

I think I've solved it by applying the exact same logic as was discussed in an earlier question I asked in August 2022

This doesn't work:

like "*" & [forms]![frmContactSearch].[Form]![txtContactForename] & "*" OR Is Null

but this does:

like "*" & [forms]![frmContactSearch].[Form]![txtContactForename] & "*" OR [forms]![frmContactSearch].[Form]![txtContactForename] Is Null

To reiterate, it was nothing to do with the checkbox, rather my inability to use criteria correctly.

Pete

 

[ebs17]

A query displays those records for which the overall filter returns True.

The Three-Valued Logic of SQL

Consideration of individual cases:

? "abc" Like NULL, "abc" Like "*" & NULL & "*",  "abc" Like "*c*"
Null          True          True

? "" Like NULL, "" Like "*" & NULL & "*",  "" Like "*c*"
Null          True          False

? NULL Like NULL, NULL Like "*" & NULL & "*",  NULL Like "*c*"
Null          Null          Null

With the filter, you have to look at both sides, i.e. the content of the table field and the content of the control. If NULL content occurs, it must be additionally treated or checked.

It is also important to note that a Boolean field in a table and a CheckBox in a form can also have the content NULL (before the first use) in addition to True or False.