how to custom sort strings

Posted on 2013-09-11
Medium Priority
Last Modified: 2013-09-11
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!! = ))
Question by:developingprogrammer
  • 4
  • 2
  • 2
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 39482780
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


Author Comment

ID: 39482830
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?
LVL 61

Expert Comment

ID: 39482895
<<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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39483593
<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".


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39483619
...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 & "'")

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39483621
Again, no points wanted for my posts above...
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39483777
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...

Author Closing Comment

ID: 39485467
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!! = ))

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question