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. Oftentimes, code is not the issue. It's the data. In this case, what was throwing us off was that the first two digits of your account ID are actually a 'separate' piece of data from the rest of your account ID. That's why breaking it up like this made it work right. Just food for thought for the future. :)
Happy CF'ing! Dave -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2008 4:10 PM To: cf-newbie Subject: Re: Numerical order of query results YES! This is working perfectly! I am grateful. One favor: Would you mind letting me explain back to you 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 some zeros. Now, if you have 7 digits in Clco (like 0110192 ) you start at the 3rd digit and go to the end which counts 5 digits. So you take 5 from 10 = 5. Add those 5 zeros and then the account number without the first two digits 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 then, I would never have come up with it on my own. But, I can see how this will help me in a number of things I have to sort numerically, where there are some letters involved too sometimes. Thanks for sticking with me. You have been an enormous help. Lewis -----Original Message----- From: Dave Phillips <[EMAIL PROTECTED]> To: cf-newbie <[email protected]> Sent: Wed, 29 Oct 2008 4:10 pm Subject: RE: Numerical order of query results Missing a ) for the replicate() function: ORDER BY SUBSTRING(a.Clco,1,2)+REPLICATE('0', 10 - EN(SUBSTRING(a.Clco,3,LEN(a.Clco))))+ SUBSTRING(a.Clco,3,LEN(a.ClCo)) -----Original Message----- rom: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] ent: Wednesday, October 29, 2008 3:06 PM o: cf-newbie ubject: Re: Numerical order of query results Here you go. RDER BY SUBSTRING(a.Clco,1,2)+REPLICATE('0', 10 - EN(SUBSTRING(a.Clco,3,LEN(a.Clco)))+ SUBSTRING(a.Clco,3,LEN(a.ClCo)) ----Original Message----- rom: Dave Phillips <[EMAIL PROTECTED]> o: cf-newbie <[email protected]> ent: Wed, 29 Oct 2008 4:00 pm ubject: RE: Numerical order of query results Copy and paste the ORDER BY you used please. ave ----Original Message----- om: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nt: Wednesday, October 29, 2008 2:41 PM : cf-newbie bject: Re: Numerical order of query results got this error. correct 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:4125 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
