Heenan, Lambert
Lambert.Heenan at AIG.com
Tue Oct 21 11:37:16 CDT 2003
I withdraw my suggestion. It does not handle nEn or nDn.
> -----Original Message-----
> From: Heenan, Lambert [SMTP:Lambert.Heenan at aig.com]
> Sent: Tuesday, October 21, 2003 12:25 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Sorting Alpha-Numerics
>
> This should be a touch faster than Gustav's as it only uses one function
> call vs. five.
>
> SELECT Table1.TT
> FROM Table1
> ORDER BY IsNumeric([tt]), Table1.TT;
>
> Here's the (abridged) output...
>
> 000
> 000
> 001
> 001
> 002
> 002
> 003
> 004
> 005
> 006
> 007
> 008
> 009
> 010
> 011
> 012
> ...
> 995
> 996
> 997
> 998
> 999
> 1A1
> 1a1
> 1B2
> a23
> ZZZ
>
> Lambert
>
> > -----Original Message-----
> > From: Mitsules, Mark [SMTP:Mark.Mitsules at ngc.com]
> > Sent: Tuesday, October 21, 2003 12:13 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: RE: [AccessD] Sorting Alpha-Numerics
> >
> > Holy cow...I'm in awe. I remember a quote from this list a long time
> ago
> > that seems appropriate at the moment. It read something like "Gustav is
> a
> > legend...cha cha cha"
> >
> > Thank you sir,
> >
> >
> > Mark
> >
> >
> >
> > -----Original Message-----
> > From: Gustav Brock [mailto:gustav at cactus.dk]
> > Sent: Tuesday, October 21, 2003 11:57 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Sorting Alpha-Numerics
> >
> >
> > Hi Mark
> >
> > Oh yeah, one more go to format leading zeroes:
> >
> > .. Abs(StrComp(strVal, Right("00" & CStr(Val(strVal)), 3), 1))
> >
> > /gustav
> >
> >
> > >> Order By Abs(StrComp(strVal, Cstr(Val(strVal)), 1)) ASC, strVal ASC
> >
> > > Hmm...on this iteration 100-999 sorted properly first, the remaining
> > > records reverted back to the way it was before...
> >
> > > 001
> > > .
> > > 009
> > > 00A
> > > .
> > > 00Z
> > > 010
> > > .
> > > Etc...
> >
> >
> >
> > > Mark
> >
> >
> > > -----Original Message-----
> > > From: Gustav Brock [mailto:gustav at cactus.dk]
> > > Sent: Tuesday, October 21, 2003 11:11 AM
> > > To: Access Developers discussion and problem solving
> > > Subject: Re: [AccessD] Sorting Alpha-Numerics
> >
> >
> > > Hi Mark
> >
> > >> Oooh...that is a little closer Gustav. You must be on the right
> > >> track. That approach put 001 through 019 in order before jumping to
> > >> 01A.
> >
> > > Of course ... StrComp returns -1 for that 01A.
> >
> > > This should do:
> >
> > > Order By Abs(StrComp(strVal, Cstr(Val(strVal)), 1)) ASC, strVal ASC
> >
> > > /gustav
> >
> >
> > >> You could do this SQL
> >
> > >> .. Order By StrComp(strVal, Cstr(Val(strVal)), 1) ASC, strVal ASC
> >
> > >> /gustav
> >
> >
> > >>> 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
> >
> > _______________________________________________
> > 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