By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

How to use checkboxes in a query

P: 61
Hi All,

I am currently compiling a database for use in the workplace. At the moment I am doing queries for this database.

The problem I am encountering is that I am creating queries with a form so that I can utilize drop down lists. This is all working fine but now I am trying to do a query using checkboxes.

When I set up the query I told that if e.g. the Runout checkbox is ticked the criteria is "Like *-1*" else it is "Like *0*". I am using an IIF statement to do this but it doesn't seem to be working.

I was wondering if anyone could help?


p.s. If you need more infor just ask...
Mar 29 '10 #1
Share this Question
Share on Google+
5 Replies

P: 83
Something like this
Expand|Select|Wrap|Line Numbers
  1. iif (Forms!YourFormName.CheckboxName = True,"truepart","falsepart")
Why would you use Like....
Mar 29 '10 #2

Expert Mod 15k+
P: 31,492
I'm afraid that whoever fed you that information was mistaken. They appear to be confusing boolean data with string data. You would need something like :
Expand|Select|Wrap|Line Numbers
  1. IIf(Me.[CheckBoxName], TruePart, FalsePart)
If the value returned is a boolean (as is the case with CheckBoxes generally - but they can also return Nulls in some circumstances) then there is no need to convert the value into a boolean (which is essentially what is done when comparing say X = 1).

Does that clarify things for you?
Mar 29 '10 #3

P: 61
Thanks for the posts guys but the code isn't working,

I am looking for the code to use in Expression builder in the criteria field of my query, that's why I had it set to give me the "Like" boolean answers to compare to my main table.

I have only a little code on the form for my ok and cancel buttons. When I try to run the form's query it tells me there is a problem with the OK buttons code that won't let me open the query. This is the code below:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "qryDrawingFeatures", acViewNormal, acEdit
Where my code says acViewNormal seems to be the problem as when I hover the pointer over this section it tells me acViewNormal=0.


Mar 30 '10 #4

P: 61
I have found the solution to this problem. I was scouring the internet and found a website that told me not to use quotation marks and the Like operator. An example of the code I compiled is as follows:

Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![frmDrawingFeatures]![tbRunout]=-1,-1,0)
Now when I run the code it gives me all the checkboxes that are ticked the same as on my form.

Thanks for the help guys

Mar 30 '10 #5

Expert Mod 15k+
P: 31,492
I considered leaving you in blissful ignorance on that, bearing in mind you had a solution that worked, but in all conscience I couldn't leave that as the best answer. Certainly it works, but as I explained in an earlier post, it is doing things quite the wrong way around.

My earlier suggestion didn't work as I didn't take into consideration that it was to be used in SQL, and obviously you hadn't yet shared the information required to arrive at the solution you managed eventually.

With that information, the simple solution you require, that doesn't complicate unnecessarily, is :
Expand|Select|Wrap|Line Numbers
  1. Forms!frmDrawingFeatures.tbRunout
There is really no need to use a complicated IIf() to test the result, simply to return the value tested in all circumstances. If there were more CheckBoxes to test in the filter, it may look like this (formulated as a SQL WHERE clause) :
Expand|Select|Wrap|Line Numbers
  1. WHERE Forms!frmDrawingFeatures.tbRunout,
  2.   AND Forms!frmDrawingFeatures.tbCheckBox2,
  3.   AND etc
Mar 30 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.