query relelated to 2 combo boxes

[tbmarlie]

I have a form with 2 combo boxes, Company and Tax ID. The users have
3 options for picking the criteria from these 2 combo boxes. (1) Both
can be chosen OR (2) The company can be chosen and the tax id can be
left blank OR (3) The tax id can be chosen and the company can be left
blank.

Lets say, we have the following table where there are 10 records, all
of which have tax id equal to 123456 and 5 of the companies are 00001
and 5 of the companies are 00002. In that case if the user chooses
123456 from the tax id combo box and leaves the company combo box
null, all ten records should show up in the results. I am trying to
set up a query which has 3 criteria lines for each of the possible
combinations shown above, but when I have one of the criteria lines
having [Forms]![frm_Main]![Txt_TaxID] in the criteria for tax id, what
can I put in the criteria for the Company criteria if the user has
left that blank(ie, null). If I put null (because that is what it is
on the form), then nothing will get chosen. If I just leave it blank,
then everything will get chosen, but I can’t do this because since
this criteria line is just one of 3 criteria options, I will always
get the 10 records if either of the other 2 options is choses because
having the criteria on separate lines is basically using an "OR"
expression. Is there a way of getting this to work on a query or do I
have to use a macro or vb?

 

[John Spencer]

Try the following

WHERE (TaxID = Forms!FormName!ComboTax or Forms!FormName!ComboTax is null)
AND (Company = Forms!FormName!ComboCompany Or Forms!FormName!ComboCompany is Null)

The easiest way to get this set up is to do it in SQL view or enter the
following as criteria on one line

Field: TaxID
Criteria: = Forms!FormName!ComboTax or Forms!FormName!ComboTax is null

Field: Company
Criteria: = Forms!FormName!ComboCompany or Forms!FormName!ComboCompany is null

When you close the query and then reopen it you will find that Access has
added two additional columns to your query and has restructured the criteria
into four criteria lines. The query will still work.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[tbmarlie]

Try the following

WHERE (TaxID = Forms!FormName!ComboTax or Forms!FormName!ComboTax is null)
AND (Company = Forms!FormName!ComboCompany Or Forms!FormName!ComboCompany is Null)

The easiest way to get this set up is to do it in SQL view or enter the
following as criteria on one line

Field: TaxID
Criteria: = Forms!FormName!ComboTax or Forms!FormName!ComboTax is null

Field: Company
Criteria: = Forms!FormName!ComboCompany or Forms!FormName!ComboCompany is null

When you close the query and then reopen it you will find that Access has
added two additional columns to your query and has restructured the criteria
into four criteria lines.  The query will still work.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have a form with 2 combo boxes, Company and Tax ID.  The users have
3 options for picking the criteria from these 2 combo boxes. (1) Both
can be chosen OR (2) The company can be chosen and the tax id can be
left blank OR (3) The tax id can be chosen and the company can be left
blank.

Lets say, we have the following table where there are 10 records, all
of which have tax id equal to 123456 and 5 of the companies are 00001
and 5 of the companies are 00002.  In that case if the user chooses
123456 from the tax id combo box and leaves the company combo box
null, all ten records should show up in the results.  I am trying to
set up a query which has 3 criteria lines for each of the possible
combinations shown above,  but when I have one of the criteria lines
having [Forms]![frm_Main]![Txt_TaxID] in the criteria for tax id, what
can I put in the criteria for the Company criteria if the user has
left that blank(ie, null).  If I put null (because that is what it is
on the form), then nothing will get chosen.  If I just leave it blank,
then everything will get chosen, but I can’t do this because since
this criteria line is just one of 3 criteria options, I will always
get the 10 records if either of the other 2 options is choses because
having the criteria on separate lines is basically using an "OR"
expression.  Is there a way of getting this to work on a query or do I
have to use a macro or vb?- Hide quoted text -

- Show quoted text -

Thanks John. I never would have figured this one out. I set it up
yesterday using some conditional macros, but this query, but itself,
looks like its more robust because it also accounts for the situation
where both fields are null. Thanks again.