[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

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
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","*")

Open in new window

0
jenica024
Asked:
jenica024
  • 4
  • 3
  • 3
2 Solutions
 
pcelbaCommented:
The Like operator needs two operands but you have just one. The column name from your table is missing. One possible solution is in attached code. For me it seems more probable to use AND operators. If you tell what the condition should test and what columns should be included then we may help better.
ColumnA Like IIf(([Forms]![frmPR]![chkA])=True,"A","*")  Or 
ColumnB Like IIf(([Forms]![frmPR]![chkB])=True,"B","*") Or 
ColumnC Like IIf(([Forms]![frmPR]![chkC])=True,"C","*") Or 
ColumnD Like IIf(([Forms]![frmPR]![chkD)=True,"D","*")

Open in new window

0
 
shru_0409Commented:
0
 
shru_0409Commented:
IIf(([Forms]![frmPR]![chkA])=True,"A","*")
 Or IIf(([Forms]![frmPR]![chkB])=True,"B","*")
 Or IIf(([Forms]![frmPR]![chkC])=True,"C","*")
 Or IIf(([Forms]![frmPR]![chkD)=True,"D","*")
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
shru_0409Commented:
0
 
jenica024Author Commented:
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]),"*",[Forms]![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. :(
0
 
pcelbaCommented:
Then you need to create a simple pattern:


Dim Pattern As String
Pattern = IIf(([Forms]![frmPR]![chkA])=True,"A","") + 
          IIf(([Forms]![frmPR]![chkB])=True,"B","") +
          IIf(([Forms]![frmPR]![chkC])=True,"C","") +
          IIf(([Forms]![frmPR]![chkD])=True,"D","")
Pattern = IIf(Pattern = "", "*", "[" + Pattern + "]")
 
'and use the Pattern in Like operation:
TypeCol Like Pattern 

Open in new window

0
 
jenica024Author Commented:
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...
0
 
jenica024Author Commented:
thank you for the help. :)
0
 
pcelbaCommented:
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","") + "]", "*") 

Open in new window

0
 
jenica024Author Commented:
Sorry to be asking this late...what is TypeCol?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now