Heenan, Lambert
Lambert.Heenan at AIG.com
Tue Oct 21 11:59:33 CDT 2003
Last word on this? May or may not be faster than Gustav's but it (looks)
simpler (to me!), and it handles the E and D problem as well as sorting 00A
after 999...
SELECT Table1.TT
FROM Table1
ORDER BY IsNumeric([tt]) And Mid([TT],2,1) Not In ("D","E"), Table1.TT;
which has two function calls and two Boolean operators, giving this
output...
996
997
998
999
00a
00A
1A1
1a1
1B2
1D4
1d4
1e4
1E4
2e3
3d4
a23
ZZZ
Lambert
> -----Original Message-----
> From: Mitsules, Mark [SMTP:Mark.Mitsules at ngc.com]
> Sent: Tuesday, October 21, 2003 12:39 PM
> To: 'Heenan, Lambert'
> Subject: RE: [AccessD] Sorting Alpha-Numerics
>
> Thank you Lambert, but this is turning out to be a "one-off". I've
> decided
> to take Gustav's resulting query and turn it into a make-table
> query...then,
> I'll just use the newly created autonumber field for sorting purposes.
> But,
> at first glance, I would be expecting 00A after 999 and before 1A1...did I
> miss something in your approach?
>
>
>
> Mark
>
>
> -----Original Message-----
> From: Heenan, Lambert [mailto:Lambert.Heenan at AIG.com]
> Sent: Tuesday, October 21, 2003 12:25 PM
> To: 'Access Developers discussion and problem solving'
> Cc: Mitsules, Mark S. (Newport News)
> 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