How to put your own text in the first row of Combo / List box

[Guest]

I have a combo box on form, it is based on a query (only 1 field ). Now I
want my text "NoOne" to display on the first row of the Combo box.

I tried to make one table, add a field value to "NoOne", but when I join the
query it is sorted in Ascending or decending order, it is mixed inbetween
somwhere. But my requirement is to bring this particular text on the first
row.

I cannot add any additional records in the query to obtain this.

Please advise, what method I should move to do this.

Thanks.

 

[Brendan Reynolds]

Here are two possible solutions. The first works by placing parentheses
around the literal text. This will ensure that it appears at the top of the
list when the list is sorted in ascending alphabetical order ...

SELECT tblTest.TestText
FROM tblTest
UNION SELECT '(NoOne)'
FROM tblTest
ORDER BY tblTest.TestText

This works as long as a) you don't mind the parentheses and b) the list is
always sorted in ascending order.

An alternative that doesn't require the parentheses is ...

SELECT tblTest.TestText, 1 AS KeepTop
FROM tblTest
UNION SELECT 'NoOne', 0
FROM tblTest
ORDER BY KeepTop ASC, tblTest.TestText

If you want to keep the literal text at the top of the list, while sorting
the remainder of the list in descending instead of ascending order, you can
do that simply by tagging on a 'DESC' at the end ...

SELECT tblTest.TestText, 1 AS KeepTop
FROM tblTest
UNION SELECT 'NoOne', 0
FROM tblTest
ORDER BY KeepTop ASC, tblTest.TestText DESC