"deb" wrote in message
...
access 2003

I have a combobox with the below row source. It contains a Union to add
"All units".

How can I edit this to only show "All Units" if there are more than one
selection in the combobox?

i.e.
if combobox rowsource has 3 units then "All Units" would be included.
if combobox rowsource has only 1 unitthen only list the one unit.

SELECT t000GFacts.UnitID, t000GFacts.Unit, t000GFacts.UnitType,
t000GFacts.AEIC, t000GFacts.PlantName, t000GFacts.Unit AS UnitOrder,
t000GFacts.SiteName, t000GFacts.OperatingCompany AS Customer,
t000GFacts.ProjectNo, t000GFacts.SiteID, t000GFacts.PlantID,
t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN
t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON
t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE
(((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID]))

UNION SELECT 0 AS UnitID, "All Units" AS Unit, "" AS UnitType, "" AS AEIC,
"" AS PlantName, "" AS UnitOrder, "" AS SiteName, "" AS Customer, "" AS
ProjectNo, "" AS SiteID, "" AS PlantID, t040Project.ProjectID FROM
t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON
t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID =
t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like
[Forms]![f001ProjectReview]![ProjectID]))
ORDER BY t000GFacts.PlantName, t000GFacts.Unit;


I'm not sure I'm going to get this right without setting up tables to test
with, but try this:

SELECT t000GFacts.UnitID, t000GFacts.Unit, t000GFacts.UnitType,
t000GFacts.AEIC, t000GFacts.PlantName, t000GFacts.Unit AS UnitOrder,
t000GFacts.SiteName, t000GFacts.OperatingCompany AS Customer,
t000GFacts.ProjectNo, t000GFacts.SiteID, t000GFacts.PlantID,
t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN
t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON
t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE
(((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID]))
UNION ALL
SELECT 0 AS UnitID, "All Units" AS Unit, "" AS UnitType, "" AS AEIC,
"" AS PlantName, "" AS UnitOrder, "" AS SiteName, "" AS Customer, "" AS
ProjectNo, "" AS SiteID, "" AS PlantID, t040Project.ProjectID FROM
t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON
t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID =
t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like
[Forms]![f001ProjectReview]![ProjectID]))
GROUP BY 0, t040Project.ProjectID
HAVING Count(*) 0
ORDER BY t000GFacts.PlantName, t000GFacts.Unit;


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)