Sort text field based on specific order - NOT alphabetical

[landlord]

I have an Access DB with a text field that can contain one of 4 words:
Daily, Periodic, Seasonal, Other. I want to sort on this field so the
query result is in the same order: Daily, Periodic, Seasonal, Other
(not alphabetical). Is there a way to make this happen without
manually entering a corresponding number in a seperate field and then
sorting on the number field?
Thanks.

 

[NetworkTrade]

well if you used the term zOther ....then it would be just alphabetical...

but you can use that sort numerical field and not require that it be manual
nor even visible.... add into the 'AfterUpdate' event for the term the
if/when statements to populate the sort field....i.e.

If Me.TermField="Daily" then
Me.SoftField=1
End If

Select Case method is more elegant but with just 4 options it isn't a
biggie....

 

[Allen Browne]

Create a little lookup table, with 4 records for the 4 words.
The table will have fields like this:
FrequencyTypeID Text primary key
SortOrder Number lower numbers sort first
Enter the 4 records, and the numbers 1 to 4.

You can now use a combo box to enter these values.
Just use a query to sort them correctly.

There are other convoluted solutions including Choice() or nested IIf()s, or
Switch(), but nothing as simple and easy to maintain as the lookup table
(which is the correctly normalized solution anyway.)