Checkboxes and Queries - HELP

[FaithSpins]

Hi,

I have to run a query based upon what checkboxes are ticked/sellected.

I have a form with 5 checkboxes (unbound). I also have a query that includes those 5 checkbox (status 1,2,3,4,5) plus the field/control Company Name (from my Contact table). My Contact table has Company details including their status. And the status (which might be more than one) is what the query is made of.

If the end user choses checkboxes 1 and 3 (for example) then all Companies that have their status set to 1 and 3 should come up in a query. "Should"...but I'm getting all of them or I when opening the form and clicking on the Run Query button I get parameter boxes that pop up.

I'm at a loss. Can anyone direct me?

Thank you!

 

[rockyjr]

Try something like this:

Select TableName.Field1, TableName.Field2, TableName.Field3
FROM TableName
WHERE (isnull(Forms![FormName]!chkbox1) Or tableName.Field1 = True)
And (isnull(Forms![FormName]!chkbox2) Or tableName.Field2 = True)
And (isnull(Forms![FormName]!chkbox3) Or tableName.Field3 = True)

That should do it.

 

[FaithSpins]

ok, I tried that - but it's not working (my code is below)

The table name is Contacts. the field names I'm guessing are those in Contacts that have the checkboxes and the company names.

I get a Compile Error:

"Expected: line number or label or statement or end of statement" after each AND line.

Now I did this coding behind a button...am I supposed to put code in the query? Or maybe I need one of those other things it mentions? Or am I missing some ( or )s?


I am new to this, really. Very green!

Here's the code:


Private Sub cmdQueryPortStat_Click()


Select Contacts.Company , Contacts.FoF, Contacts.NHF, Contacts.SM, Contacts.FC, Contacts.CO

from Contacts

WHERE (IsNull(Forms![Portfolio_Status]!cb1) Or Contacts.FoF = True)
And (IsNull(Forms![Portfolio_Status]!cb2) Or Contacts.NHF= True)
And (IsNull(Forms![Portfolio_Status]!cb3) Or Contacts.SM = True)
And (IsNull(Forms![Portfolio_Status]!cb4) Or Contacts.FC = True)
And (IsNull(Forms![Portfolio_Status]!cb5) Or Contacts.CO = True)

End Sub

Thank you very very much!

 

[rockyjr]

The code looks good... just not at the right place.

So, if I understand, you have a form with the checkboxes... a subform in that form right?

The code should be entered into your query....go to design view of the query and go to the SQL view and enter the code there.

Now, your subform should have that query has a Record Source.

That should do the trick!