Problems With Check Box Queries 1

[mcongdon]

How would I use a form that has 14 check boxes to query representatives that have the preferences specified by the check box. I created a query:

SELECT tbl_CORPBONDPREF.AAA, tbl_CORPBONDPREF.[Investment Grade], tbl_CORPBONDPREF.[Less than Investment Grade], tbl_CORPBONDPREF.[1-5 Years], tbl_CORPBONDPREF.[5-10 Years], tbl_CORPBONDPREF.[10-20 Years], tbl_CORPBONDPREF.[20+ Years], tbl_CORPBONDPREF.[5], tbl_CORPBONDPREF.[10], tbl_CORPBONDPREF.[25], tbl_CORPBONDPREF.[25+], tbl_CORPBONDPREF.Discount, tbl_CORPBONDPREF.Par, tbl_CORPBONDPREF.Premium
FROM tbl_CORPBONDPREF
GROUP BY tbl_CORPBONDPREF.AAA, tbl_CORPBONDPREF.[Investment Grade], tbl_CORPBONDPREF.[Less than Investment Grade], tbl_CORPBONDPREF.[1-5 Years], tbl_CORPBONDPREF.[5-10 Years], tbl_CORPBONDPREF.[10-20 Years], tbl_CORPBONDPREF.[20+ Years], tbl_CORPBONDPREF.[5], tbl_CORPBONDPREF.[10], tbl_CORPBONDPREF.[25], tbl_CORPBONDPREF.[25+], tbl_CORPBONDPREF.Discount, tbl_CORPBONDPREF.Par, tbl_CORPBONDPREF.Premium
HAVING (((tbl_CORPBONDPREF.AAA)=[Forms]![frm_CORPPREFSEARCH]![AAA]) AND ((tbl_CORPBONDPREF.[Investment Grade])=[Forms]![frm_CORPPREFSEARCH]![Investment Grade]) AND ((tbl_CORPBONDPREF.[Less than Investment Grade])=[Forms]![frm_CORPPREFSEARCH]![Less than Investment Grade]) AND ((tbl_CORPBONDPREF.[1-5 Years])=[Forms]![frm_CORPPREFSEARCH]![1-5 Years]) AND ((tbl_CORPBONDPREF.[5-10 Years])=[Forms]![frm_CORPPREFSEARCH]![5-10 Years]) AND ((tbl_CORPBONDPREF.[10-20 Years])=[Forms]![frm_CORPPREFSEARCH]![10-20 Years]) AND ((tbl_CORPBONDPREF.[20+ Years])=[Forms]![frm_CORPPREFSEARCH]![20+ Years]) AND ((tbl_CORPBONDPREF.[5])=[Forms]![frm_CORPPREFSEARCH]![5]) AND ((tbl_CORPBONDPREF.[10])=[Forms]![frm_CORPPREFSEARCH]![10]) AND ((tbl_CORPBONDPREF.[25])=[Forms]![frm_CORPPREFSEARCH]![25]) AND ((tbl_CORPBONDPREF.[25+])=[Forms]![frm_CORPPREFSEARCH]![25+]) AND ((tbl_CORPBONDPREF.Discount)=[Forms]![frm_CORPPREFSEARCH]![Discount]) AND ((tbl_CORPBONDPREF.Par)=[Forms]![frm_CORPPREFSEARCH]![Par]) AND ((tbl_CORPBONDPREF.Premium)=[Forms]![frm_CORPPREFSEARCH]![Premium]));

The problem with this is that it returns only representatives with exactly the configuration of check boxes that is on the form. I might just want to check one box and see anyone that meets that criteria regardless of the other check boxes. I'm new to all this and I can't quite figure it out.

 

[PHV]

something like this ?

SELECT DISTINCT AAA, [Investment Grade], [Less than Investment Grade], [1-5 Years], [5-10 Years], [10-20 Years], [20+ Years], [5], [10], [25], [25+], Discount, Par, Premium
FROM tbl_CORPBONDPREF
WHERE AAA=[Forms]![frm_CORPPREFSEARCH]![AAA]
AND ([Investment Grade]=[Forms]![frm_CORPPREFSEARCH]![Investment Grade]
  OR [Less than Investment Grade]=[Forms]![frm_CORPPREFSEARCH]![Less than Investment Grade]
  OR [1-5 Years]=[Forms]![frm_CORPPREFSEARCH]![1-5 Years]
  OR [5-10 Years]=[Forms]![frm_CORPPREFSEARCH]![5-10 Years]
  OR [10-20 Years]=[Forms]![frm_CORPPREFSEARCH]![10-20 Years]
  OR [20+ Years]=[Forms]![frm_CORPPREFSEARCH]![20+ Years]
  OR [5]=[Forms]![frm_CORPPREFSEARCH]![5]
  OR [10]=[Forms]![frm_CORPPREFSEARCH]![10]
  OR [25]=[Forms]![frm_CORPPREFSEARCH]![25]
  OR [25+]=[Forms]![frm_CORPPREFSEARCH]![25+]
  OR Discount=[Forms]![frm_CORPPREFSEARCH]![Discount]
  OR Par=[Forms]![frm_CORPPREFSEARCH]![Par]
  OR Premium=[Forms]![frm_CORPPREFSEARCH]![Premium]
);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traingamer]

PHV, won't that simply return everything that matches a checked or unchecked box?

To match on just the checked boxes, something like:

...
[b]AND (nz([Forms]![frm_CORPPREFSEARCH]![1-5 Years], 1) OR
[/b] ([1-5 Years]=[Forms]![frm_CORPPREFSEARCH]![1-5 Years]))

  AND (nz([Forms]![frm_CORPPREFSEARCH]![5-10 Years], 1) OR
 ([5-10 Years]=[Forms]![frm_CORPPREFSEARCH]![5-10 Years]))

...

etc?
i.e. for each criteria, if the checkbox is blank OR the data matches a checked box then select the record.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[mcongdon]

Thanks PHV!
That query returns all the entries because it is also matching the blank check boxes on the form with blank check boxes in the table. I wish I could show you a picture of the form, but I can try and explain the table.

tbl_CORPBONDPREF
Persh#
AAA
Investment Grade
Less Than Investment Grade
1-5 Years
5-10 Years
10-20 Years
20+ Years
5
10
25
25+
Discount
Par
Premium

Each of these fields have check boxes as their type. Persh# is the primary key of the table with all the representative's contact information. When we enter a new representative, we make a new entry in this table to assign preferences for corporate bonds. There is no option control in these check boxes. They are in groups, but a rep could have no interest in the product and leave them all blank, or they could prefer 6 characteristics and check those 6 boxes.

I would like to be able to search for all reps that have an interest in Par bonds by checking the par box on the search form, then having it return a report from the query with all the representatives' names and numbers that have an interest in Par bonds. If I want to search for reps that are interested in Par bonds with a maturity of 10-20 Years, then it should return only rep names that have an interest in BOTH Par bonds and 10-20 Year Maturities.

I hope this is a bit clearer now, I've been working on it a while and can't quite figure it out, thats why I turned to you guys! ;-)

 

[mcongdon]

Thanks Traingamer!
I used that code, but it was matching it way too exact. It wouldn't give me the name of a rep unless every single check box was exactly right combination of check boxes. I need it to be able to search for reps as I described directly above this post.
Thanks!

 

[traingamer]

Tested:

...
AND (not([Forms]![frm_CORPPREFSEARCH]![1-5 Years]) OR
([1-5 Years]=[Forms]![frm_CORPPREFSEARCH]![1-5 Years]))

  AND (not([Forms]![frm_CORPPREFSEARCH]![5-10 Years], 1) OR
 ([5-10 Years]=[Forms]![frm_CORPPREFSEARCH]![5-10 Years]))
...

Right idea - wrong code before.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[mcongdon]

That works perfectly, Thank you so much!