How to use checkboxes in a query

[greeni91]

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?

/Sandy

p.s. If you need more infor just ask...

[orangeCat]

Something like this

iif (Forms!YourFormName.CheckboxName = True,"truepart","falsepart")

Why would you use Like....

[NeoPa]

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'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 :

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?

[greeni91]

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:

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.

Thanks,

/Sandy

[greeni91]

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:

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

/Sandy

[NeoPa]

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 :

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) :

WHERE Forms!frmDrawingFeatures.tbRunout,
  AND Forms!frmDrawingFeatures.tbCheckBox2,
  AND etc