Boolean expressions in the where statement

[Paul Washburn]

Im trying to build a form with multiple check boxes that each apply a filter
to the records displayed. I need for the user to be able to select any
combination of the checkboxes and have the resulting records filtered
accordingly.

I was thinking something along the lines of a "select" or statement or an
if...else loop, but unfortunately im fairly new with sql am having trouble
with the syntax, assuming its even possible.

Something along the linse of:

Select * from Table Where
If (chkA = true) then chkA
Else (chkB = true) then chkB
else (chkA = true) and (chkB = true) then chkA and chkB
else *;

Any help would be greatly appreciated.

 

[KARL DEWEY]

Try this --
SELECT *
FROM Table
WHERE IIF(chkA = True AND chkB = True, Achk, IIF(chkA = True, Achk,
IIF(chkB = True, Bchk))) OR IIF(chkA = True AND chkB = True, Bchk, IIF(chkA
= True, Achk, IIF(chkB = True, Bchk)));

First part - if A & B true A, then check for singular true. Second part -
if A & B true B, then check for singular true.

 

[KenSheridan via AccessMonster.com]

I replied to this in one of your earlier threads:

http://www.accessmonster.com/Uwe/Fo...rm-with-Multiple-Check-boxes#a0405459301aeuwe


Translating what I said then to your current example:

SELECT *
FROM

WHERE ([chkA] = Forms![YourForm]![chkA]
OR Forms![YourForm]![chkA] = FALSE)
AND ([chkB] = Forms![YourForm]![chkB]
OR Forms![YourForm]![chkB] = FALSE);

The logic behind this was explained in the other thread. Note the point I
made there about the possibility of the unbound check boxes being Null when
the form opens.

Ken Sheridan
Stafford, England

 

[Paul Washburn]

Thanks,

I got the query working with 2 variables, but im getting a syntax error
(missing operator) when i try to add a third, ive got 4 total to incoprorate.

Heres what ive got so far:

SELECT *
FROM bncforms_table
WHERE Iif (Forms![FormRpt]![chkEdi] = True and Forms![FormRpt]![chkWeather]
= true and Forms![FormRpt][chkNoEnter] = true, ([bncforms_table]![edi] = true
and bncforms_table![Weather Ntc] = true and bncforms_table![No Enter] =
true), Iif (Forms![FormRpt]![chkEdi] = True, [bncforms_table]![edi] = true,
iif (Forms![FormRpt]![chkWeather] = true, bncforms_table![Weather Ntc] =
true, iif (Forms![FormRpt][chkNoEnter] = true, bncforms_table![No Enter] =
true, "*"))))

 

[vanderghast]

The 'example' you supply does not make sense to me, what * means, as
example, in that context?


I 'assume' that what you try do to is something like:

WHERE checkForOptionA { then use the filter }
fieldOptionA = suppliedValueForOptionA

AND checkForOptionB { then use the filter }
fieldOptionB = suppliedValueForOptionB

AND ...


If so, { then use the filet } is the operator IMP.

example:

WHERE FORMS!tshirts!checkColor IMP color = FORMS!tshirts!tshirtColor
AND FORMS!tshirts!checkSize IMP size = FORMS!tshirts!tshirtSize



which would check for the supplied color and size only if two 'matching'
check box are check; if only one check box is checked, only the matching
property will be filtered; if no check box are check, nothing will be
filtered.
(I also assume the form name is tshirts, and the controls should be
obvious. IMP is a Boolean operator, like AND , OR , ... which acts like
.... intended, here.)



Vanderghast, Access MVP

 

[KARL DEWEY]

I still can not follow. As I said the WHERE statement test a field for a
condition but I do not see how your SQL does that.

I see what appears to be these as fields --
[bncforms_table]![edi]
bncforms_table![Weather Ntc]
bncforms_table![No Enter]

And these as your check boxes --
Forms![FormRpt]![chkEdi]
Forms![FormRpt]![chkWeather]
Forms![FormRpt][chkNoEnter]

I assume that your field are Yes/No data type.

Can you build a Truth Table?

--
Build a little, test a little.

Probably not so much a method as not really knowing how this function works.

I think the way i have it written it would check if all were true, and then
check A, then B, then C. Which admittedly would run into issues if both A
and C were true, but not B.

Ideally the program needs to add the check box in question to the where
clause when check, for 4 different check boxes.

You are losing me.
SELECT *
FROM bncforms_table
WHERE Iif (Forms![FormRpt]![chkEdi] = True and Forms![FormRpt]![chkWeather]
= true and Forms![FormRpt][chkNoEnter] = true,
The above is your first test.

This part should be the test results if TRUE.
([bncforms_table]![edi] = true and bncforms_table![Weather Ntc] = true and
bncforms_table![No Enter] = true),
What is the above suppose to do?

Second test --
Iif (Forms![FormRpt]![chkEdi] = True,

[bncforms_table]![edi] = true,

iif (Forms![FormRpt]![chkWeather] = true,

bncforms_table![Weather Ntc] = true,

iif (Forms![FormRpt][chkNoEnter] = true,

bncforms_table![No Enter] = true, "*"))))

I am used to WHERE [XYZ] = IIF(Test something, Results if true, Results
if false) or nesting of second test in lieu of the first false followed by
true results and false results.

Can you explain your method?

--
Build a little, test a little.

Thanks,

I got the query working with 2 variables, but im getting a syntax error
(missing operator) when i try to add a third, ive got 4 total to incoprorate.

Heres what ive got so far:

SELECT *
FROM bncforms_table
WHERE Iif (Forms![FormRpt]![chkEdi] = True and Forms![FormRpt]![chkWeather]
= true and Forms![FormRpt][chkNoEnter] = true, ([bncforms_table]![edi] = true
and bncforms_table![Weather Ntc] = true and bncforms_table![No Enter] =
true), Iif (Forms![FormRpt]![chkEdi] = True, [bncforms_table]![edi] = true,
iif (Forms![FormRpt]![chkWeather] = true, bncforms_table![Weather Ntc] =
true, iif (Forms![FormRpt][chkNoEnter] = true, bncforms_table![No Enter] =
true, "*"))))

:

Try this --
SELECT *
FROM Table
WHERE IIF(chkA = True AND chkB = True, Achk, IIF(chkA = True, Achk,
IIF(chkB = True, Bchk))) OR IIF(chkA = True AND chkB = True, Bchk, IIF(chkA
= True, Achk, IIF(chkB = True, Bchk)));

First part - if A & B true A, then check for singular true. Second part -
if A & B true B, then check for singular true.


--
Build a little, test a little.


:

Im trying to build a form with multiple check boxes that each apply a filter
to the records displayed. I need for the user to be able to select any
combination of the checkboxes and have the resulting records filtered
accordingly.

I was thinking something along the lines of a "select" or statement or an
if...else loop, but unfortunately im fairly new with sql am having trouble
with the syntax, assuming its even possible.

Something along the linse of:

Select * from Table Where
If (chkA = true) then chkA
Else (chkB = true) then chkB
else (chkA = true) and (chkB = true) then chkA and chkB
else *;

Any help would be greatly appreciated.