Forcing an item to the bottom of a drop-down list

[barrylowe]

I have a combo box which is used to allow the to select a response source from a preset list.

The sources are in a table called lkup_RepsonseSources

The SQL behind the combo box rowsource is as follows:

SELECT lkup_ResponseSources.ResponseSourceID, [SourceRef] & " : " & [SourceDescription] AS SourceName
FROM lkup_ResponseSources
ORDER BY [SourceRef] & " : " & [SourceDescription];

What I want to do is add an "Other" option into the list, but always have it appear at the bottom of the list.

Any ideas?

 

[PHV]

You may try this SQL code:
SELECT ResponseSourceID, SourceName
FROM (SELECT ResponseSourceID, [SourceRef] & " : " & [SourceDescription] AS SourceName
FROM lkup_ResponseSources ORDER BY 2) AS A
UNION ALL SELECT 0, 'Other' FROM MSysObjects WHERE ID=2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[barrylowe]

Hey, thanks for that.

That worked OK for inserting Other into the list but the rest of the list needs to be in alphabetic order with Other coming at the end.

Any thoughts?

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[barrylowe]

It's in my original posting.

 

[PHV]

How about using MINE ?

 

[barrylowe]

I have done, but as I said in my second posting it does not order the rest of the list correctly.

It does indeed insert Other at the end of the list but the preceeding items are not in alphabetic order.

 

[PHV]

Again, which exact code did you try ?

 

[barrylowe]

The rowsource for the combo box is:

SELECT ResponseSourceID, SourceName FROM (SELECT ResponseSourceID, [SourceRef] & " : " & [SourceDescription] AS SourceName FROM lkup_ResponseSources ORDER BY 2) AS A UNION ALL SELECT 0, 'Other' FROM MSysObjects WHERE ID=2;

 

[PHV]

And the items are not sorted by SourceRef ?

 

[barrylowe]

No, they appear to be sorted by the order they were entered into the database.