Access World Forums Sorting Complex Alpha-Numeric Strings
 Register FAQ Members List Social Groups Top Posters Search Today's Posts Mark Forums Read

 01-01-2007, 10:52 AM #1 DALeffler Registered Perpetrator   Join Date: Dec 2000 Location: Golden, CO, USA Posts: 263 Thanks: 0 Thanked 2 Times in 1 Post Sorting Complex Alpha-Numeric Strings Complex Alphanumeric Sorts One of the keys to understanding how to do this and why it works is to first realize that an Access ascending text sort will sort strings of digits perfectly well provided the digit characters are right justified. For example: tTxt 001 009 010 099 100 sorts exactly as we would expect (using "0"s to right justify the digits) but typical real world numbers stored as text data: tTxt 1 10 100 9 99 gives no satisfaction with an ascending sort. We can easily manipulate the text sort order by inserting characters (Sort Codes) into the string that has the digit characters like so: tTxt 11 19 210 299 3100 Now the above text strings are sorted as we would expect. The trick is to count the significant digits for all "numbers" in the original string and insert the character that represents the number of significant digits in front of the digits in the text string (as shown above). It's relatively simple VBA coding to count how many digits are strung consecutively together before either the end of the string or a non-digit character is encountered. Once we know how many digits there are, inserting the text character representing that value into the string in front of the number sequence is also relatively straight foreward. Does it work for complex alpha-numeric strings? Let's try it: A10A1A1 A1A10A1 A1A1A1 A1A1A10 A1A1A9 A1A9A1 A9A1A1 is what an ascending text sort will return but is not intuitive, whereas: Code: ```tTxt SortCode A1A1A1 A011A011A011 A1A1A9 A011A011A019 A1A1A10 A011A011A0210 A1A9A1 A011A019A011 A1A10A1 A011A0210A011 A9A1A1 A019A011A011 A10A1A1 A0210A011A011``` is much more appealing if sorted ascending by the SortCode. Notice that I've increased the sort codes (in bold, above) to 2 characters. I've seen Serial Numbers and Model Numbers of equipment items that come close to stringing 10 digits together, but have yet to see one that strings 99 digits together. All that we're really doing is sorting numerical characters (left to right) by the length of the contiguous numerical characters and then by the characters in the number. In other words: Every "01" in the calculated SortCode string will be followed by 1 numerical character. Every "02" in the calculated SortCode string will be followed by 2 numerical characters. Every "03" in the calculated SortCode string will be followed by 3 numerical characters, etc... Since we know that any 1 digit number has to be less than any 2 digit number, and any 2 digit number has to be less than any 3 digit number, etc..., a calculated SortCode as described will sort complex alpha-numeric strings more intuitively. Another functionality we almost have to include in our SortCode generating routine is a Leading Zero counter/sort: We can't count the leading zeros of a string of digits as significant digits. "09", "009", and "0009" should all be resolved to numbers having only one significant digit. By generating and concatenating the number of leading zeros each consecutive number sequence encountered in the string has (using 2 numerical characters for how many leading Zeros there are for every resolved number in the string to be sorted) to the end of our SortCode, we will also be sorting by leading zeros. Like this (leading Zero Count/Sort shown in bold): Code: ```tTxt SortCode DA1A DA011A 00 DA1T-JP1 DA011T-JP011 0000 DA01T-JP1 DA011T-JP011 0100 DA001T-JP1 DA011T-JP011 0200 DA2S-DR02 DA012S-DR012 0001 DA02S-DR2 DA012S-DR012 0100 DA02S-DR002 DA012S-DR012 0102 DA002S-DR2 DA012S-DR012 0200 DA010A DA0210A 01 DA11T-JP1 DA0211T-JP011 0000 DAS-DR2 DAS-DR012 00 F F``` Implementation: The function code: Code: ```Function fSortCode(sPassed As String) As String Dim sLZTemp As String Dim iLZCount As Integer Dim sNumTemp As String Dim iNumCount As Integer While Len(sPassed) 'is the first character a number? If IsNumeric(Left(sPassed, 1)) Then While IsNumeric(Left(sPassed, 1)) If Left(sPassed, 1) = "0" And iNumCount = 0 Then iLZCount = iLZCount + 1 'Leading Zero counter Else iNumCount = iNumCount + 1 'significant digit counter sNumTemp = sNumTemp & Left(sPassed, 1) 'remember all significant digits End If sPassed = Mid(sPassed, 2) 'loop on next character Wend 'Leading Zeros counted, significant digits counted... sLZTemp = sLZTemp & Format(iLZCount, "00") 'leading zero sort code for later... sNumTemp = Format(iNumCount, "00") & sNumTemp fSortCode = fSortCode & sNumTemp iNumCount = 0 'reset for next imbedded digit string... iLZCount = 0 sNumTemp = "" End If 'next character not a number, loop on next character in string... fSortCode = fSortCode & Left(sPassed, 1) sPassed = Mid(sPassed, 2) Wend fSortCode = fSortCode & " " & sLZTemp 'concatenate leading zero sort code End Function``` Calling the Function: I wouid suggest: SELECT MyTableName.tMyTableFieldName FROM MyTableName ORDER BY IIf(IsNull([tMyTableFieldName]),"",fSortCode([tMyTableFieldName])); __________________ Doug (Learns from mistakes; have scars to prove it...) Last edited by DALeffler; 06-17-2007 at 10:30 PM.
 The Following 2 Users Say Thank You to DALeffler For This Useful Post: Futures_Bright (02-26-2013), LVLawson (08-19-2014)

 Thread Tools Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World     Access World News     Site Suggestions     Introduce Yourself     The Watercooler Microsoft Access Discussion     General     Tables     Queries     Forms     Reports     Macros     Modules & VBA     Theory and practice of database design     Access Web Microsoft Access Reference     Access FAQs     Code Repository     Sample Databases     Microsoft Access Tutorials     Microsoft Access User Groups Apps and Windows     SQL Server     Crystal Reports     Visual Basic     VB.NET     Word     Excel     Web Design and Development         ASP and ASP.NET         PHP & MySQL     Windows     Other Software     Hardware Questions and Answers Non-Access Issues     Politics & Current Events     Debates     Gaming     Sports, Health & Fitness     Gadgets     Small Business

 Similar Threads Thread Thread Starter Forum Replies Last Post Garindan Reports 12 03-22-2005 01:55 AM 90405 General 3 07-21-2004 01:14 PM mredmond Queries 9 10-16-2003 09:49 AM jdp103 Modules & VBA 13 10-12-2003 10:15 AM mitchem1 Queries 2 02-22-2002 11:52 AM

All times are GMT -8. The time now is 04:24 PM.

 Microsoft Access Help General Tables Queries Forms Reports Macros Modules & VBA Theory & Practice Access FAQs Code Repository Sample Databases Video Tutorials Sponsored Links How to advertise Media Kit