Adding a fixed value to a query

[Guest]

Hi,

I have a combobox which is populated from a query. It is used to set a
filter for the records shown on a form. So far so good.

BUT

I want to add a fixed entry of "All" to the top of the list....

If I create a query which says:

select "All" as Teacher

Then this works. (I get a single row returned containing a single column,
called teacher, with the value "All")

The actual query for the combo also works.

BUT

if I try to do a UNION of the two queries, I get an error message:-

Query must contain at least one table or query.

The full statement in the combo-box's Row Source is:

SELECT "ALL" as Teacher UNION SELECT OutstandingDetentions.Teacher FROM
OutstandingDetentions GROUP BY OutstandingDetentions.Teacher;

How do I add a fixed item to the top of a dynamically created list?

HELP!!!

TIA

Paul

 

[Jeff Boyce]

Paul

Try

SELECT "<ALL>" FROM OutstandingDetentions
UNION
SELECT DISTINCT Teacher FROM OutstandingDetentions
ORDER BY Teacher

(untested SQL code... try each piece separately until it works, then create
the UNION query).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Thanks, spot on!

Paul

Paul

Try

SELECT "<ALL>" FROM OutstandingDetentions
UNION
SELECT DISTINCT Teacher FROM OutstandingDetentions
ORDER BY Teacher

(untested SQL code... try each piece separately until it works, then create
the UNION query).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/