drop down based on query but also need a blank as an option

  • Thread starter Thread starter tbmarlie
  • Start date Start date
T

tbmarlie

I have a drop down on my form that is based on a fairly complex
query. Basically the drop down will allow values to show based on
whether or not the other drop downs have been picked or not. Here is
the code:

SELECT Tbl_A01_CrossRef_for_combos.Co, Tbl_A01_CrossRef_for_combos.[Co
Name], Tbl_A01_CrossRef_for_combos.[Tax ID State],
Tbl_A01_CrossRef_for_combos.Co
FROM Tbl_A01_CrossRef_for_combos
WHERE ((([Forms]![frm_Main]![Cbo_State]) Is Null) AND (([Forms]!
[frm_Main]![Combo_TaxType]) Is Null) AND (([Forms]![frm_Main]!
[Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year]) Is Null))
OR ((([Forms]![frm_Main]![Cbo_State]) Is Not Null) AND (([Forms]!
[frm_Main]![Combo_TaxType]) Is Null) AND (([Forms]![frm_Main]!
[Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year]) Is Null)
AND ((Tbl_A01_CrossRef_for_combos.State)=[Forms]![frm_Main]!
[Cbo_State])) OR (((Tbl_A01_CrossRef_for_combos.[Tax ID
State])=[Forms]![frm_Main]![Txt_TaxId]) AND (([Forms]![frm_Main]!
[Cbo_State]) Is Not Null) AND (([Forms]![frm_Main]![Combo_TaxType]) Is
Not Null) AND (([Forms]![frm_Main]![Txt_TaxId]) Is Not Null) AND
(([Forms]![frm_Main]![Txt_Year]) Is Not Null) AND
((Tbl_A01_CrossRef_for_combos.State)=[Forms]![frm_Main]![Cbo_State])
AND ((Tbl_A01_CrossRef_for_combos.[Tax Type])=[Forms]![frm_Main]!
[Combo_TaxType])) OR (((Tbl_A01_CrossRef_for_combos.Co) Is Null) AND
((Tbl_A01_CrossRef_for_combos.[Tax ID State])=[Forms]![frm_Main]!
[Txt_TaxId]) AND (([Forms]![frm_Main]![Cbo_State]) Is Not Null) AND
(([Forms]![frm_Main]![Combo_TaxType]) Is Not Null) AND (([Forms]!
[frm_Main]![Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year])
Is Not Null) AND ((Tbl_A01_CrossRef_for_combos.State)=[Forms]!
[frm_Main]![Cbo_State]) AND ((Tbl_A01_CrossRef_for_combos.[Tax
Type])=[Forms]![frm_Main]![Combo_TaxType]))
ORDER BY Tbl_A01_CrossRef_for_combos.Co,
Tbl_A01_CrossRef_for_combos.Co;

Well, the users also want to always have the option of being able to
choose a null value in addition to whatever choices show on the drop
down. Is that possible when the row source is based on a query as
apposed to a list. Thanks.
 
Try using a Union query as the source of data:

SELECT Tbl_A01_CrossRef_for_combos.Co, Tbl_A01_CrossRef_for_combos.[Co
Name], Tbl_A01_CrossRef_for_combos.[Tax ID State],
Tbl_A01_CrossRef_for_combos.Co
FROM Tbl_A01_CrossRef_for_combos
WHERE ((([Forms]![frm_Main]![Cbo_State]) Is Null) AND (([Forms]!
[frm_Main]![Combo_TaxType]) Is Null) AND (([Forms]![frm_Main]!
[Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year]) Is Null))
OR ((([Forms]![frm_Main]![Cbo_State]) Is Not Null) AND (([Forms]!
[frm_Main]![Combo_TaxType]) Is Null) AND (([Forms]![frm_Main]!
[Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year]) Is Null)
AND ((Tbl_A01_CrossRef_for_combos.State)=[Forms]![frm_Main]!
[Cbo_State])) OR (((Tbl_A01_CrossRef_for_combos.[Tax ID
State])=[Forms]![frm_Main]![Txt_TaxId]) AND (([Forms]![frm_Main]!
[Cbo_State]) Is Not Null) AND (([Forms]![frm_Main]![Combo_TaxType]) Is
Not Null) AND (([Forms]![frm_Main]![Txt_TaxId]) Is Not Null) AND
(([Forms]![frm_Main]![Txt_Year]) Is Not Null) AND
((Tbl_A01_CrossRef_for_combos.State)=[Forms]![frm_Main]![Cbo_State])
AND ((Tbl_A01_CrossRef_for_combos.[Tax Type])=[Forms]![frm_Main]!
[Combo_TaxType])) OR (((Tbl_A01_CrossRef_for_combos.Co) Is Null) AND
((Tbl_A01_CrossRef_for_combos.[Tax ID State])=[Forms]![frm_Main]!
[Txt_TaxId]) AND (([Forms]![frm_Main]![Cbo_State]) Is Not Null) AND
(([Forms]![frm_Main]![Combo_TaxType]) Is Not Null) AND (([Forms]!
[frm_Main]![Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year])
Is Not Null) AND ((Tbl_A01_CrossRef_for_combos.State)=[Forms]!
[frm_Main]![Cbo_State]) AND ((Tbl_A01_CrossRef_for_combos.[Tax
Type])=[Forms]![frm_Main]![Combo_TaxType]))
ORDER BY Tbl_A01_CrossRef_for_combos.Co,
Tbl_A01_CrossRef_for_combos.Co;
UNION
SELECT "< No Company >" AS C1, "< No Name >" AS C2,
"< No TaxID >" AS C3, NULL AS C4
FROM Tbl_A01_CrossRef_for_combos;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



tbmarlie said:
I have a drop down on my form that is based on a fairly complex
query. Basically the drop down will allow values to show based on
whether or not the other drop downs have been picked or not. Here is
the code:

SELECT Tbl_A01_CrossRef_for_combos.Co, Tbl_A01_CrossRef_for_combos.[Co
Name], Tbl_A01_CrossRef_for_combos.[Tax ID State],
Tbl_A01_CrossRef_for_combos.Co
FROM Tbl_A01_CrossRef_for_combos
WHERE ((([Forms]![frm_Main]![Cbo_State]) Is Null) AND (([Forms]!
[frm_Main]![Combo_TaxType]) Is Null) AND (([Forms]![frm_Main]!
[Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year]) Is Null))
OR ((([Forms]![frm_Main]![Cbo_State]) Is Not Null) AND (([Forms]!
[frm_Main]![Combo_TaxType]) Is Null) AND (([Forms]![frm_Main]!
[Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year]) Is Null)
AND ((Tbl_A01_CrossRef_for_combos.State)=[Forms]![frm_Main]!
[Cbo_State])) OR (((Tbl_A01_CrossRef_for_combos.[Tax ID
State])=[Forms]![frm_Main]![Txt_TaxId]) AND (([Forms]![frm_Main]!
[Cbo_State]) Is Not Null) AND (([Forms]![frm_Main]![Combo_TaxType]) Is
Not Null) AND (([Forms]![frm_Main]![Txt_TaxId]) Is Not Null) AND
(([Forms]![frm_Main]![Txt_Year]) Is Not Null) AND
((Tbl_A01_CrossRef_for_combos.State)=[Forms]![frm_Main]![Cbo_State])
AND ((Tbl_A01_CrossRef_for_combos.[Tax Type])=[Forms]![frm_Main]!
[Combo_TaxType])) OR (((Tbl_A01_CrossRef_for_combos.Co) Is Null) AND
((Tbl_A01_CrossRef_for_combos.[Tax ID State])=[Forms]![frm_Main]!
[Txt_TaxId]) AND (([Forms]![frm_Main]![Cbo_State]) Is Not Null) AND
(([Forms]![frm_Main]![Combo_TaxType]) Is Not Null) AND (([Forms]!
[frm_Main]![Txt_TaxId]) Is Null) AND (([Forms]![frm_Main]![Txt_Year])
Is Not Null) AND ((Tbl_A01_CrossRef_for_combos.State)=[Forms]!
[frm_Main]![Cbo_State]) AND ((Tbl_A01_CrossRef_for_combos.[Tax
Type])=[Forms]![frm_Main]![Combo_TaxType]))
ORDER BY Tbl_A01_CrossRef_for_combos.Co,
Tbl_A01_CrossRef_for_combos.Co;

Well, the users also want to always have the option of being able to
choose a null value in addition to whatever choices show on the drop
down. Is that possible when the row source is based on a query as
apposed to a list. Thanks.
Click to expand...
 
In ACCESS, I recall that you can have WHERE and ORDER BY clauses within each
individual subquery for a UNION query. I know that SQL Server doesn't allow
that, though.

I'd have to refresh my memory to be more definitive.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


Try using a Union query as the source of data:
Click to expand...

Ken -
The union would go after the first "FROM" clause, NOT after the
"WHERE" clause.
 
I've confirmed my memory that what I posted as a query will work just fine
in ACCESS SQL, though the ORDER BY clause in the first subquery will not be
honored in the final dataset (to get a desired ORDER BY to be applied, put
in in the last subquery).
 
Back
Top