how to custom sort strings

hey guys,

ok i know there are many better ways of doing this, but i just wanna try and push the boundaries and learn a bit.

i've got a table field that is populated by Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday.

there are very few records in this table - just a settings table.

so when i'm querying this table i want to sort it such that monday appears at the top, then tueday, then wednesday etc.

so far the solution i've thought of is using a calculated values column in my query.

switch(field = "monday",1,field = "tuesday",2 ....... etc)

then i will sort on that calculated column.

is there a better way of doing it guys? how would yall do it?

let's just assume that we MUST have the field as text "Monday, Tuesday, Wednesday ...." --> so no cheating by normalising!! hahaha = PP

= )) thanks guys!! = ))
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Create a table (weekdays are just an example), with a text field for your Text, and a numeric field for its numeric representation:

For example, tblLU_Weekdays:

wkDay  WeekDayValue
Monday       1
Tuesday       2
Wednesday  3

Then in your query:

SELECT t.*, lu.WeekDayValue
FROM YourTable t INNER JOIN tblLU_Weekdays lu ON t.WkDay = lu.wkDay
ORDER BY lu.WeekDayValue

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developingprogrammerAuthor Commented:
Thanks mbizup!! Ha but I'm trying to figure out how to sort custom strings. Is this the only way? Converting it into a numerical value, whether in a separate column or using a formula?

How can we apply a custom sort to text? As in what are the other ways?
<<but I'm trying to figure out how to sort custom strings>>

That's exactly what that lookup table does.  

It takes strings and gives them non-alphabetical sorting values.  (You can do this with weekdays, fruits, statuses or anything else that you want to put in order, but not necessarily alphabetize).

You certainly CAN do it in other ways that involve embedding VBA functions such as SWITCH, IIF or your own custom functions into your queries. In fact the SWITCH statement you posted does exactly the same thing as the table based approach.

But to me, a lookup table joined in your query is a cleaner solution that is more readable, easier to maintain and likely has better performance.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
<No points wanted as mbizup has answered your question here.>

yes, ...having day "string" values sort correctly is also a problem in crosstab queries, and charts as well.
So the table approach would be a good investment at this point...

I tired each of the "function/IIF/Switch" approaches at one point or another.
The problem was that there was always a situation where it would not work, and had to be troubleshot (user spelling errors, for ex: "Wensday")

....a lookup table/combobox approach eliminates this issue because selecting the Day "'string", simultaneously selects the Day "Value".


Jeffrey CoachmanMIS LiasonCommented:
...and remember, it is always easier to reference a numeric value (in VBA and in SQL), than a text value:
For example (Numeric, ...easy):
Dlookup("YourField","YourTable", "YourFiled=" & YourValue)

String (.....uuuugh!...)
Dlookup("YourField","YourTable", "YourFiled=" & "'" & YourValue & "'")

Jeffrey CoachmanMIS LiasonCommented:
Again, no points wanted for my posts above...
Jeffrey CoachmanMIS LiasonCommented:
A standard date table will be this:
DayValue      DayName
1                      Sunday
2                      Monday
3                      Tuesday
4                      Wednesday
5                      Thursday
6                      Friday
7                      Saturday

But you can set your own order if you like, (as mbiziup stated) ...just be aware that what is above is what Access treats each day as internally.

So by setting your own order, you may create conflicts with what Access thinks the day value is and what your custom sort order says the day is...
developingprogrammerAuthor Commented:
whao ok great! thanks so much mbizup for your solution and Jeff thanks so much for taking the time to explain all this to me! really appreciate it!! = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.