Solved

Checkbox parameter query syntax

Posted on 2009-07-03
10
335 Views
Last Modified: 2013-11-28
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
Comment
Question by:jenica024
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 24772689
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
 
LVL 14

Expert Comment

by:shru_0409
ID: 24772717
0
 
LVL 14

Assisted Solution

by:shru_0409
shru_0409 earned 100 total points
ID: 24772873
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
Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

 
LVL 14

Expert Comment

by:shru_0409
ID: 24772877
0
 

Author Comment

by:jenica024
ID: 24773089
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
 
LVL 42

Accepted Solution

by:
pcelba earned 400 total points
ID: 24773224
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
 

Author Comment

by:jenica024
ID: 24776916
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
 

Author Closing Comment

by:jenica024
ID: 31599578
thank you for the help. :)
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24777251
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
 

Author Comment

by:jenica024
ID: 24842381
Sorry to be asking this late...what is TypeCol?
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question