Combo Box - Custom Sort Order

[twildt]

I have a combo box with the following entries:

Document
Other
Process
Validate
Verify

I want the list sorted so that "Other" is at the bottom. Everything else is alphabetical ascending. Is this possible?

[ranman256]

Add a 2nd virtual field in the query called SortOrder build it as:
select *, [choice]<>'Other' AS SortOrder from tChoices Sort by [SortOrder]

the selection OTHER value will show FALSE and sort to the bottom,

[dunc723]

Is your row source a table or query? If it does not have to be, a simpler way is to just put the choices in the Row Source property as text in the desired order, separated by semicolons: Document;Process;Validate;Verify;Other

[twildt]

what do you mean by "virtual" field?

[twildt]

Ok, here is what I came up with so far.

1. I made a query that adds a calculated field.

qry_SrtType
...SrtType
...WorkType

Document 1
Other 2
Process 1
Validate 1
Verify 1

SELECT IIf([Work Type]="Other",2,1) AS SrtType, [Work Type]
FROM Work_Type;

This works! The query in datasheet view is perfectly sorted!!

2. I add the following code to my form's combo box
SELECT [Work Type] FROM qry_SrtType ORDER BY SrtType, [Work Type];

This is not working. The combo box is empty. I added that code above to the "Control Source" and I also tried the "Row Source".

The Control Source used to be a field in a table because I need that table to update when a selection is made and the user hits the "Save" button.

What am I doing wrong? Why is my list empty when the query works? And how can I set it up so that when the user hits save on the form it updates the table "Work Hours"?