Numeric Sorting

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Okay, here's my specific problem. I work at a school.
Instead of grades, we use class colors (eg Red, Orange,
Blue, etc.) As you might imagine I use this grouping
criteria quite often on roll calls, lists, and reports out
the ying yang. Because the colors are not in alpha order
I use a Switch Command in the query to assign a numeric
value to each color then I sort the resulting number. In
Access 97, I had no problems. Since upgrading to access
2k3, everything is sorting alphabetically e.g. 1,10,2
etc. Since the value is a calculated field not stored in
a table, how do i sort numerically. I can't set a field
to numeric when it only exists in the recordset or can i?
Let me know.
 
Try this:

CalculatedFieldName: CInt(Switch("Red", 1, "Orange", 2, "Blue", 3))
 
Scott,
Another solution would be to add a table of the Class Colors and there sortOrder
and then join to that table whenever you need to sort by your special order.
This has the advantage of allowing you to change the order throughout the
database by editing the table, adding a new color and putting it in the order
without modifying every switch statment in your database.

Table: ClassSort
Field: ClassColor
Field: SpecialOrder

Then just add that table to all the relevant queries and join the ClassColor
fields in the two relevant tables and add SpecialOrder to your query fields. At
this point your sort is simple.
 
Back
Top