Sorting Fields 1

[jvet4]

I have several tables that have the same primary key which is called CaseNum and is a text field.&nbsp;&nbsp;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.&nbsp;&nbsp;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?<br><br>Thanks

 

[kathryn]

Nope.&nbsp;&nbsp;Sorry, but if a field is text, it will sort alphabetically, not numerically.<br><br>Can you split your field into two fields, maybe CaseNum and CaseLetter?&nbsp;&nbsp;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?<br><br>Another option is to create a table that only contains two fields, CaseNum and SortOrder.&nbsp;&nbsp;You could create a form to maintain the sort order (it would require some coding).&nbsp;&nbsp;Then any time you want to sort, include this new table in a query and sort on SortOrder.<br><br>Just some ideas.&nbsp;&nbsp;Hope this helps. <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[elizabeth]

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 &quot;&&quot;. For instance: <b>FirstPart & SecondPart</b>

 

[jvet4]

Kathryn & Elizabeth,<br><br>Thanks for the suggestions.&nbsp;&nbsp;Since my Access skills are fairly simplistic, I decided to go the easy road.&nbsp;&nbsp;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.&nbsp;&nbsp;I think that should work (Hopefully)<br><br>Thanks again for the help.<br><br>Jason

 

[elizabeth]

Great idea!