jenica024
asked on
Checkbox parameter query syntax
I can't seem to get the following code to work.
frmPR has several combo boxes and 4 checkboxes that the user can select (one or more). I would like it to filter the results based on what the user checks - whether it be just A, or A and B, etc. I also need it to display ALL records if the user does not check anything. I was able to get it to filter based on the checkboxes but it wouldn't display all if no checkboxes are selected. Or I was able to display all but it wouldn't filter properly. :(
There are other fields with Like Iif... filters that are currently working right now (but not tied to checkboxes, just combo boxes). I am trying to turn one of the combo boxes into a check box since the user needs to select one or more of the contents.
What am i doing wrong?
Please and thank you,
j
frmPR has several combo boxes and 4 checkboxes that the user can select (one or more). I would like it to filter the results based on what the user checks - whether it be just A, or A and B, etc. I also need it to display ALL records if the user does not check anything. I was able to get it to filter based on the checkboxes but it wouldn't display all if no checkboxes are selected. Or I was able to display all but it wouldn't filter properly. :(
There are other fields with Like Iif... filters that are currently working right now (but not tied to checkboxes, just combo boxes). I am trying to turn one of the combo boxes into a check box since the user needs to select one or more of the contents.
What am i doing wrong?
Please and thank you,
j
Like IIf(([Forms]![frmPR]![chkA])=True,"A","*") Or Like IIf(([Forms]![frmPR]![chkB])=True,"B","*") Or Like IIf(([Forms]![frmPR]![chkC])=True,"C","*") Or Like IIf(([Forms]![frmPR]![chkD)=True,"D","*")
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or check this link
http://www.takveen.com/ams/detail.asp?iArt=389&iType=26
http://www.takveen.com/ams/detail.asp?iArt=389&iType=26
ASKER
Sorry - to explain better:
i have a column called "Type" - which contains either A, B, C or D. Before I just used:
Like IIf(IsNull([Forms]![frmPR] ![cboType] ),"*",[For ms]![frmPR ]![cboType ])
which worked really well because the user can select a type from the combo box in a form and it will show all A's or B's, etc. Also, if the user selected none it showed all records.
Now they want to be able to select one or more types. Say A and B or B and C, etc. I thought putting check boxes was the way to go..but if you have other suggestions I would explore those too. I could only get one of the conditions working - say i could get the checkboxes to filter out one or more types but when the boxes aren't checked it wouldn't show all records.
shru_0949:
i got this far before and it did work for the checkbox selections but it would not print all if no checkboxes were selected. :(
i have a column called "Type" - which contains either A, B, C or D. Before I just used:
Like IIf(IsNull([Forms]![frmPR]
which worked really well because the user can select a type from the combo box in a form and it will show all A's or B's, etc. Also, if the user selected none it showed all records.
Now they want to be able to select one or more types. Say A and B or B and C, etc. I thought putting check boxes was the way to go..but if you have other suggestions I would explore those too. I could only get one of the conditions working - say i could get the checkboxes to filter out one or more types but when the boxes aren't checked it wouldn't show all records.
shru_0949:
i got this far before and it did work for the checkbox selections but it would not print all if no checkboxes were selected. :(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i just want to clarify: i will need to put this in a module and in design view of the query in the criteria field use it?
thanks so very much...
thanks so very much...
ASKER
thank you for the help. :)
If you need to put it into query then you have to create user function or write it as one expression, e.g. this way:
TypeCol LIKE IIf( ([Forms]![frmPR]![chkA]) OR
([Forms]![frmPR]![chkB]) OR
([Forms]![frmPR]![chkC]) OR
([Forms]![frmPR]![chkD]), "[" +
IIf(([Forms]![frmPR]![chkA]),"A","") +
IIf(([Forms]![frmPR]![chkB]),"B","") +
IIf(([Forms]![frmPR]![chkC]),"C","") +
IIf(([Forms]![frmPR]![chkD]),"D","") + "]", "*")
ASKER
Sorry to be asking this late...what is TypeCol?
Open in new window