If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Boolean expressions in the where statement
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. |
#2
|
|||
|
|||
Boolean expressions in the where statement
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. "Paul Washburn" wrote: 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. |
#3
|
|||
|
|||
Boolean expressions in the where statement
I replied to this in one of your earlier threads:
http://www.accessmonster.com/Uwe/For...405459301aeuwe Translating what I said then to your current example: SELECT * FROM [Table] 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 wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1 |
#4
|
|||
|
|||
Boolean expressions in the where statement
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, "*")))) "KARL DEWEY" wrote: 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. "Paul Washburn" wrote: 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. |
#5
|
|||
|
|||
Boolean expressions in the where statement
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 "Paul Washburn" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|