Using Checkboxes to select multiple fields in an iif query (1 Viewer)

youngerpants

New member
Local time
Today, 17:40
Joined
Dec 1, 2005
Messages
5
Hi all,


Firstly, I'm an ex-fulltime access developer who has found himself doing access work again 6 years later; I can't actually believe how much I've forgotten :s

Anyway, I've done a search and havn't found anything that can help me so wondered if anyone could give some advice.

I have an access form with 3 combo boxes and a checkbox next to each of them. these are accessed using a query with 3 iif statements in it stating; (iif checkbox is null, "*", combo_box_value). the whole query looks like this

SELECT Customers.*
FROM Customers
WHERE (((Customers.Partner)=IIf(Forms!frm_rpt_main!check_partner Is Null,"*",Forms!frm_rpt_main!partner)) AND ((Customers.[Type Business])=IIf(Forms!frm_rpt_main!check_type Is Null,"*",Forms!frm_rpt_main!type)) AND ((Customers.[Year End Month])=IIf(Forms!frm_rpt_main!check_month Is Null,"*",Forms!frm_rpt_main!month)));

However, the query only seems to want to pull data from all 3 combo boxes

My issue is that if a checkbox is not ticked, I want the values to be ignored for all 3 combo's.

I've attached a copy of the mdb file as my description probably doesnt make any sense, the specific query is "qry_select_month_partner_type_wname_frm_rpt"


Thanking you all in advance for your help!

Younger
 

Attachments

  • Copy of db_lr.zip
    41.8 KB · Views: 213

youngerpants

New member
Local time
Today, 17:40
Joined
Dec 1, 2005
Messages
5
Sorry, I should have added that each seperate iif works independently of the others, its only when all 3 are there that I have issues

Cheers

Y
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:40
Joined
Feb 19, 2002
Messages
42,971
SELECT Customers.*
FROM Customers
WHERE (Customers.Partner=Forms!frm_rpt_main!check _partner or Forms!frm_rpt_main!partner Is Null) AND (Customers.[Type Business]=Forms!frm_rpt_main!check_type or Forms!frm_rpt_main!type Is Null) AND (Customers.[Year End Month]=Forms!frm_rpt_main!check_month or Forms!frm_rpt_main!month Is Null);
 

youngerpants

New member
Local time
Today, 17:40
Joined
Dec 1, 2005
Messages
5
Hi Pat,

Thanks for getting back to me. However the solution you gave me doesn't seem to work (I'm getting no results whatsoever). Any other ideas?

Many thanks

Younger
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:40
Joined
Feb 19, 2002
Messages
42,971
Did you modify the query in any way? Which values are you providing? Are you positive that the data exists?
 

youngerpants

New member
Local time
Today, 17:40
Joined
Dec 1, 2005
Messages
5
I did make a slight modification to the query, but only to correct a syntax error (there was a space at "check_partner" where there shouldnt have been).

The data being fed from the combo boxes comes from the same table that is being queried, so I know that the data exists.

However, when the query runs with no data in any of the combo boxes and no check boxes ticked I get all data returned but when I try to run with just one variable, I get no data returned at all.

I can probably fill in some dummy data in the database for you if it would help matters?
 

youngerpants

New member
Local time
Today, 17:40
Joined
Dec 1, 2005
Messages
5
It's OK, got it working!

You had the check variable and the combo variables the wrong way around, made the change and all is working as it should

Many thanks for your help!

Younger
 

Users who are viewing this thread

Top Bottom