Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Sorting Fields

Sorting Fields

Sorting Fields

I have several tables that have the same primary key which is called CaseNum and is a text field.  The data in this field is contains mostly numeric entries, however there are a few entries that contain letters(i.e. 1,2,2a,3,3a...)I want to be able to view these in order (from case 1 to Case 30)but I am having trouble doing it.  Is there a way to sort text data so that it will be in order from 1 to 30 (not 1,10,11,12...2,20,21,22...)that will also recoginize that 14 and 14a should be in that order?


RE: Sorting Fields

Nope.  Sorry, but if a field is text, it will sort alphabetically, not numerically.

Can you split your field into two fields, maybe CaseNum and CaseLetter?  However, to make the combination of the two fields a primary key, you have to have text in both fields, so that CaseLetter would have to contain _something_, maybe a space?

Another option is to create a table that only contains two fields, CaseNum and SortOrder.  You could create a form to maintain the sort order (it would require some coding).  Then any time you want to sort, include this new table in a query and sort on SortOrder.

Just some ideas.  Hope this helps.


RE: Sorting Fields

Another option I've had pretty good success with is to use the data type Text but but limit leading characters to numbers, so that you force the user to enter things like 01, 02 or 001, 002. Then everything sorts very nicely. This may not work if the a value in a text filed must link to a value in a numeric field. You may have to change the associated fileds in the other tables to match. I've also used this approach in comibation with Kathyn's suggestion. Also don't forget if you separate the field into two fileds, you can easily display it as one field using the concatenation character "&". For instance: FirstPart & SecondPart

RE: Sorting Fields

Kathryn & Elizabeth,

Thanks for the suggestions.  Since my Access skills are fairly simplistic, I decided to go the easy road.  Instead of trying to split the fields, I just changed the numbering system to numeric decimal and am using .1,.2,.3 instead of a,b,c.  I think that should work (Hopefully)

Thanks again for the help.


RE: Sorting Fields

Great idea!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close