How to Filter a Check Box in a Query

[doyle60]

What do I put on the criteria row for a Yes/No field to filter a query
from a print form?

The checkbox control on the form is in triple state. When it is
checked, I want all checked to appear, and when unchecked, I want only
unchecked to appear. And when gray (the triple state), I want all to
appear. I tried this:

Like (IIf(IsNull([Forms]![QualitySearchfrm]![QCompleteChosen]),"*","|
[Forms]![QualitySearchfrm]![QCompleteChosen]|"))

where "QCompleteChosen" is the checkbox on the form. But it doesn't
work. Null is not the same as being gray, I suppose.

Thanks,

Matt

 

[Allen Browne]

You can't just type that into the criteria.
Instead switch to SQL View, and set up the WHERE clause like this:

WHERE (([Forms]![QualitySearchfrm]![QCompleteChosen] Is Null)
OR (QCompleteChosen = [Forms]![QualitySearchfrm]![QCompleteChosen]))

If you are designing this for other users, they might find the triple-state
check box confusing. For example, if they use WinXP or Vista, with the
default setup there won't be any visible difference between False and Null.

 

[doyle60]

I can't quite find the syntax because I have to add it into a query
that already has other criteria in it. How do I add it into the
below? Thanks.

Matt

SELECT QualitySearchqry02.PO, QualitySearchqry02.MinOfDelivery,
QualitySearchqry02.MinOfSchedETD, QualitySearchqry02.CategoryName,
QualitySearchqry02.GroupName, QualitySearchqry02.RNProduct,
QualitySearchqry02.RNClass, QualitySearchqry02.RNLabel,
QualitySearchqry02.StoreName, QualitySearchqry02.FactoryName,
QualitySearchqry02.SubFact, QualitySearchqry02.SalesRepLastName,
QualitySearchqry02.Master, QualitySearchqry02.QComplete,
QualitySearchqry02.QCompleteDate, QualitySearchqry02.Date
FROM QualitySearchqry02
WHERE (((QualitySearchqry02.MinOfDelivery)>=(IIf(IsNull([Forms]!
[QualitySearchfrm]![DelDateLow]),#11/14/1980#,[Forms]!
[QualitySearchfrm]![DelDateLow])) And
(QualitySearchqry02.MinOfDelivery)<=(IIf(IsNull([Forms]!
[QualitySearchfrm]![DelDateHigh]),#12/31/2999#,[Forms]!
[QualitySearchfrm]![DelDateHigh]))) AND
((QualitySearchqry02.CategoryName) Like (IIf(IsNull([Forms]!
[QualitySearchfrm]![CategoryChosen]),"*","|[Forms]![QualitySearchfrm]!
[CategoryChosen]|"))) AND ((QualitySearchqry02.StoreName) Like
(IIf(IsNull([Forms]![QualitySearchfrm]![StoreChosen]),"*","|[Forms]!
[QualitySearchfrm]![StoreChosen]|"))) AND
((QualitySearchqry02.FactoryName) Like (IIf(IsNull([Forms]!
[QualitySearchfrm]![FactoryChosen]),"*","|[Forms]![QualitySearchfrm]!
[FactoryChosen]|"))) AND ((QualitySearchqry02.SubFact) Like
(IIf(IsNull([Forms]![QualitySearchfrm]![SubFactChosen]),"*","|[Forms]!
[QualitySearchfrm]![SubFactChosen]|"))) AND
((QualitySearchqry02.SalesRepLastName) Like (IIf(IsNull([Forms]!
[QualitySearchfrm]![RepChosen]),"*","|[Forms]![QualitySearchfrm]!
[RepChosen]|"))) AND ((QualitySearchqry02.Master) Like
(IIf(IsNull([Forms]![QualitySearchfrm]![MasterChosen]),"*","|[Forms]!
[QualitySearchfrm]![MasterChosen]|"))) AND
((QualitySearchqry02.Date)>=(IIf(IsNull([Forms]![QualitySearchfrm]!
[DateWritLow]),#11/14/1980#,[Forms]![QualitySearchfrm]![DateWritLow]))
And (QualitySearchqry02.Date)<=(IIf(IsNull([Forms]![QualitySearchfrm]!
[DateWritHigh]),#12/31/2999#,[Forms]![QualitySearchfrm]!
[DateWritHigh]))));

 

[doyle60]

Thanks for you time but I've decided to change the check box field
into a "Yes"/"No" text field (with an IIf statement) in a query and
work with code I understand better. Thanks again,

Matt