Impossible? (Cross-posted in Forms forum)

[jennifer_cracchiola]

I'm probably trying to do the impossible here.

I want to have a union query in a combo box to select "all" AND have
that combobox filter off of another.

I can get the first part of code working, the second part of code
working, but when I try to join into one SQL statement is bombs out.

Help!

Code Snippet for combobox filter:
SELECT Region_Country_Code_tbl.Country, Region_Country_Code_tbl.Region
FROM Region_Country_Code_tbl
WHERE
(((Region_Country_Code_tbl.Region)=IIf(IsNull([Forms]![Form2]![combo26]),[Region],[Forms]![Form2]![combo26])))
ORDER BY Region_Country_Code_tbl.Country


Code Snippet for Union query (to populate option of "all")
SELECT Region_Country_Code_tbl.Country FROM Region_Country_Code_tbl
UNION (Select "(All)" as Country From Region_Country_Code_tbl GROUP
BY Region_Country_Code_tbl.Country);


And here's where I'm at, which clearly isn't working.
SELECT Region_Country_Code_tbl.Country, Region_Country_Code_tbl.Region
FROM Region_Country_Code_tbl UNION (Select "(All)" as
Region_Country_Code_tbl.Country From Region_Country_Code_tbl, null from
Region_Country_Code_tbl) where Region_Country_Code_tbl.Region =
Forms![new_adhoc_maker_frm]![combo292];

Please help this damsel in distress! Thanks in advance!!!

Jen

 

[Dirk Goldgar]

I'm probably trying to do the impossible here.

I want to have a union query in a combo box to select "all" AND have
that combobox filter off of another.

I can get the first part of code working, the second part of code
working, but when I try to join into one SQL statement is bombs out.

Help!

Code Snippet for combobox filter:
SELECT Region_Country_Code_tbl.Country, Region_Country_Code_tbl.Region
FROM Region_Country_Code_tbl
WHERE
(((Region_Country_Code_tbl.Region)=IIf(IsNull([Forms]![Form2]![combo26])
,[Region],[Forms]![Form2]![combo26])))
ORDER BY Region_Country_Code_tbl.Country


Code Snippet for Union query (to populate option of "all")
SELECT Region_Country_Code_tbl.Country FROM Region_Country_Code_tbl
UNION (Select "(All)" as Country From Region_Country_Code_tbl GROUP
BY Region_Country_Code_tbl.Country);


And here's where I'm at, which clearly isn't working.
SELECT Region_Country_Code_tbl.Country, Region_Country_Code_tbl.Region
FROM Region_Country_Code_tbl UNION (Select "(All)" as
Region_Country_Code_tbl.Country From Region_Country_Code_tbl, null
from Region_Country_Code_tbl) where Region_Country_Code_tbl.Region =
Forms![new_adhoc_maker_frm]![combo292];

Please help this damsel in distress! Thanks in advance!!!

Did you try

SELECT
Country,
Region
FROM Region_Country_Code_tbl
WHERE
Region = Forms![new_adhoc_maker_frm]![combo292]
UNION
SELECT
"(All)" As Country,
Null As Region
FROM
Region_Country_Code_tbl;

?