Thanks again Dave. You're right, the biggest value is the learning experience.
Lewis -----Original Message----- From: Dave Phillips <[EMAIL PROTECTED]> To: cf-newbie <[email protected]> Sent: Wed, 29 Oct 2008 5:29 pm Subject: RE: Numerical order of query results Yes, you got it! And no problem. That's what we are all here for. By the way, the key to this solution was understanding your data. ftentimes, code is not the issue. It's the data. In this case, what was hrowing us off was that the first two digits of your account ID are ctually a 'separate' piece of data from the rest of your account ID. hat's why breaking it up like this made it work right. Just food for hought for the future. :) Happy CF'ing! Dave -----Original Message----- rom: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] ent: Wednesday, October 29, 2008 4:10 PM o: cf-newbie ubject: Re: Numerical order of query results YES! This is working perfectly! I am grateful. One favor: Would you mind letting me explain back to ou what I think you did and correct me if I'm wrong? I can see it takes the first two digits of the account id, and then adds ome zeros. Now, if you have 7 digits in Clco (like 0110192 ) you start at he 3rd digit and go to the end which counts 5 digits. So you take 5 from 0 = 5. Add those 5 zeros and then the account number without the first two igits to that. Like so 01 00000 10192 So 0110192 becomes 010000010192 for sorting purposes. Is this right? Man, that's clever, and actually fairly simple, once you get the idea. But hen, I would never have come up with it on my own. But, I can see how this ill help me in a number of things I have to sort numerically, where there re some letters involved too sometimes. Thanks for sticking with me. You have been an enormous help. Lewis -----Original Message----- rom: Dave Phillips <[EMAIL PROTECTED]> o: cf-newbie <[email protected]> ent: Wed, 29 Oct 2008 4:10 pm ubject: RE: Numerical order of query results issing a ) for the replicate() function: RDER BY SUBSTRING(a.Clco,1,2)+REPLICATE('0', 10 - N(SUBSTRING(a.Clco,3,LEN(a.Clco))))+ SUBSTRING(a.Clco,3,LEN(a.ClCo)) -----Original Message----- om: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nt: Wednesday, October 29, 2008 3:06 PM : cf-newbie bject: Re: Numerical order of query results ere you go. DER BY SUBSTRING(a.Clco,1,2)+REPLICATE('0', 10 - N(SUBSTRING(a.Clco,3,LEN(a.Clco)))+ SUBSTRING(a.Clco,3,LEN(a.ClCo)) ----Original Message----- om: Dave Phillips <[EMAIL PROTECTED]> : cf-newbie <[email protected]> nt: Wed, 29 Oct 2008 4:00 pm bject: RE: Numerical order of query results Copy and paste the ORDER BY you used please. ve ---Original Message----- m: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] t: Wednesday, October 29, 2008 2:41 PM cf-newbie ject: Re: Numerical order of query results got this error. orrect syntax near ')'. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4126 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
