Add row to combo box

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a comb box with the following select statement in
the "Row Source" property,

SELECT [Diagnosis].[Diagnostic_group] FROM [Diagnosis]
WHERE [Diagnosis].[Diagnostic_group] <> "Edit_list"
GROUP BY [Diagnosis].[Diagnostic_group]

This works great except for reports I want to allow the
user to be able to select "All". The Diagnosis table does
not include an "All" record. Is there a way to add
an "All" to the combox while still using my select
statement above, without adding "All" to the Diagnosis
table?

Thank you for your assistance. John
 
Hi John

Add in the "All" using a UNION query. Also, there is no need to use a GROUP
BY clause here - the DISTINCT predicate will ensure only one instance of
each value:

SELECT DISTINCT [Diagnosis].[Diagnostic_group] FROM [Diagnosis]
WHERE [Diagnosis].[Diagnostic_group] <> "Edit_list"
UNION
SELECT "All" FROM [Diagnosis]

Note that the last FROM [Diagnosis] could be any table - it's just there to
satisfy the syntax.
 
Yep! Use a union query to supply the [All] like this:

Select Distinct Diagnostic_group From Diagnosis Where Diagnostic_group <>
'Edit_list'
Union Select '[All]' From Diagnosis Order by 1

Ron W
 
Back
Top