Mitsules, Mark
Mark.Mitsules at ngc.com
Tue Oct 21 11:04:25 CDT 2003
There are other conditions that may need addressing before using this approach. Examples: Any combination of (numeric [1-9] & "D" or "E" & numeric [1-9]). Val(1D3)=1000 Val(9D9)=9000000000 Val(1E1)=10 Other situations: Val(1C1)=1 Val(8CW)=8 Val(5C9)=5 Val(90Q)=90 Mark -----Original Message----- From: Drew Wutka [mailto:DWUTKA at marlow.com] Sent: Tuesday, October 21, 2003 11:27 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Sorting Alpha-Numerics Do a double sort. Create another field, which is the VAL of your data. Sort that descending, and if the val=0 then set it to 1000, that put's it after the other numeric numbers, then sort alphabetically. Drew -----Original Message----- From: Mitsules, Mark [mailto:Mark.Mitsules at ngc.com] Sent: Tuesday, October 21, 2003 9:35 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Sorting Alpha-Numerics That worked for the small dataset you've shown, but as you can see below, an ASCII sort order will place 00A through 00Z before 010. I was hoping for a possible use of Val() in combination with the sort to produce my desired result. 008 009 00A 00B . . 00Z 010 Mark -----Original Message----- From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com] Sent: Tuesday, October 21, 2003 10:24 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Sorting Alpha-Numerics Mike, I just did regular 'sort ascending' got this (I typed it not in any order); is that what u r looking for ? r 000 001 123 134 444 445 455 456 67U AER AQO ASD FGT IUY VBG ZZZ > Greetings, > > I have a text field containing all possible 3 digit alpha-numeric > combinations from 000 to ZZZ. I am looking for a method to sort this > text field where 000 through 999 are listed in numerical order before > the remaining alpha-numeric combinations are listed in ASCII sort > order. Any suggestions? I have tried to do this in Excel, however, > Excel makes some startling decisions on its own...such as turning 7E9 > into 7000000000. > > Example: > 000 > 001 > . > . > 998 > 999 > 00A > 00B > . > . > ZZZ > > > Mark > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com ----------------------------------------- Get Breaking News from CNN, ABC, NBC, CBS Now. http://www.xuppa.com/news/?link=webmail _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com