checkbox doesn't filter query

R

rbchildrn

Hello. I have a table that includes a yes/no column for whether or not
we send mail to each record. I need to run a query that doesn't show
those who have the box unchecked. I can't find an answer for this
seemingly simple problem in the posts.

I have verified that all records in the table have a value in the
yes/no (check box) field, and the query returns all records whether my
criteria is True, -1, or Not False. It seems like it should work, it
just doesn't.

Let me know what other info you need. I do have other criteria for
other fields in the query - could they be interfering somehow?

Here is the SQL:

SELECT Trim(TblPeople.NamePrefix & " " & TblPeople.FirstName & " " &
TblPeople.MiddleInitial & " " & TblPeople.LastName & " " &
TblPeople.NameSuffix & " " & (IIf(TblPeople.[Family Address?]=True,"and
Family",Null))) AS PrintName, TblPeople.Address1, TblPeople.Address2,
TblPeople.City, TblPeople.State, TblPeople.Zip

FROM TblPeople INNER JOIN [QryDonation Totals Ind] ON TblPeople.Id =
[QryDonation Totals Ind].Id

WHERE (((TblPeople.City)=NZ([Forms]![Create Data Source for
Mailings].[Text2],[TblPeople].[City])) AND (([QryDonation Totals
Ind].SumOfDonationAmount)>=NZ([Forms]![Create Data Source for
Mailings].[Text0],0)) AND ((TblPeople.[Addressee?])=True) AND
((TblPeople.MembershipStatus)="Active") AND
((TblPeople.[Adult?])=True)) OR ((([QryDonation Totals
Ind].SumOfDonationAmount) Is Null));

The first part adds some fields together so that I can match fields for
a Union Query with Organization mailing info. There is also
referenced a query that totals donation amounts per person. In the
WHERE statement I am referencing text boxes on forms where the user can
enter minimum donation total or City.

FYI: The MembershipStatus field is also returning all results, not
just "Active". However, the "Family Address?" function I have in
there works great, as do the City and Donation filters.
 
R

rbchildrn

Yeah, I've tried -1/0, True/False, and even into the Not False!
Perhaps there is some kind of bug? Is there such a thing as too many
criteria? Thanks for the prompt response
 
R

rbchildrn

I can fix the problem with the checkboxes and status if I remove the
Donations Sum Query from the mix, but I'd like to be able to keep this
in there. Any suggestions about how to use queries as criteria in
other queries?
 
L

Lung

I might not be answering your question, but at a very basic level I've found
that if you enter the following in the criteria field of the Query:
1) No criteria gets you ALL the records
2) "Is Null" gets you the empty records, and
3) "Is Not Null" gets you the non-empty records.
4) Any other criteria (e.g., Like, <, =, etc.) gets you what you
specified but no empty records are included.

This seems kind of obvious but it's easy to be careless if you don't
specifically include the "Null" word when needed. For example, if I enter
.... Not Like "No", you should get all records except the "Null" records and
"No" records. Therefore, if there are records with empty spaces in that
field, those will not be included unless you instead enter ... Is Null or Not
Like "No". In your case where you've specified criteria in fields other than
the Y/N field, you potentially will end up excluding a lot of records
unintentionally unless you include the Null word.

-----------------------------------------------------------------------------------------

[email protected] said:
Hello. I have a table that includes a yes/no column for whether or not
we send mail to each record. I need to run a query that doesn't show
those who have the box unchecked. I can't find an answer for this
seemingly simple problem in the posts.

I have verified that all records in the table have a value in the
yes/no (check box) field, and the query returns all records whether my
criteria is True, -1, or Not False. It seems like it should work, it
just doesn't.

Let me know what other info you need. I do have other criteria for
other fields in the query - could they be interfering somehow?

Here is the SQL:

SELECT Trim(TblPeople.NamePrefix & " " & TblPeople.FirstName & " " &
TblPeople.MiddleInitial & " " & TblPeople.LastName & " " &
TblPeople.NameSuffix & " " & (IIf(TblPeople.[Family Address?]=True,"and
Family",Null))) AS PrintName, TblPeople.Address1, TblPeople.Address2,
TblPeople.City, TblPeople.State, TblPeople.Zip

FROM TblPeople INNER JOIN [QryDonation Totals Ind] ON TblPeople.Id =
[QryDonation Totals Ind].Id

WHERE (((TblPeople.City)=NZ([Forms]![Create Data Source for
Mailings].[Text2],[TblPeople].[City])) AND (([QryDonation Totals
Ind].SumOfDonationAmount)>=NZ([Forms]![Create Data Source for
Mailings].[Text0],0)) AND ((TblPeople.[Addressee?])=True) AND
((TblPeople.MembershipStatus)="Active") AND
((TblPeople.[Adult?])=True)) OR ((([QryDonation Totals
Ind].SumOfDonationAmount) Is Null));

The first part adds some fields together so that I can match fields for
a Union Query with Organization mailing info. There is also
referenced a query that totals donation amounts per person. In the
WHERE statement I am referencing text boxes on forms where the user can
enter minimum donation total or City.

FYI: The MembershipStatus field is also returning all results, not
just "Active". However, the "Family Address?" function I have in
there works great, as do the City and Donation filters.
Click to expand...
 
R

rbchildrn

I'm sorry, I don't understand what you mean. There are no records that
have null in the check box fields. As for the other fields, the query
is returning records with null fields (for example, if there is no
address2 line, I still get that record in the results). The fields
that are populated from the form include the NZ() function to account
for any null values. What change are you suggesting that I make?

Thanks
 
L

Lung

Just to be sure, when I refer to Null, I just mean that a field is empty, but
the word Null is actually written out in the criteria. I'm presuming that
you are working with a regular Table and using a regular Query to view the
desired data. If that is the case, the records with Null fields will only
show up if:
a) You have no criteria (in the Query) for that field, or
b) Your criteria says something like Like "No" or Is Null.

If you don't want to show records where the "Address2" field is missing,
then just add the criteria ... Is Not Null ... under "Address2" in the Query.
If you only want to show records where you have "Yes" for a particular
field, it should be as simple as adding ... Like "Yes" ... to the criteria
line for that field (which would exclude any records with that field being
Null, if any).

I'm guessing your problem is a lot more complicated than that and I'm
thinking this might be a case of the blind leading the blind. I suggested
the above because I have found it an easy omission to make. When it comes to
"Forms" and the NZ() function, I'm not the one to ask. I'm not sure how they
interact with Null in the criteria of a Query.
 
R

rbchildrn

The simple answer I found in a post subject: Criteria priority

When you have an Or statement (the second criteria line in the Query
Builder) in one column, you have to duplicate the criteria for the Or
row of each other column that specifies criteria. Seems strange, but
it worked.
 
Top