Crystal Reports alphanumeric sort problem

Posted on 2011-03-19
Last Modified: 2012-08-13
Hi all-

I use CR 2008 and have a field called itemCode which is a string and has values like R1, R10, R2, R44.002, R300, R9 etc. The "R" is always present and always at the beginning.
I need to sort them numericaly so that the result is R1, R2, R10 etc.  Currently it sorts like R1, R10, R2 etc.  
I need a formula to truncate the "R" and cast it to a number and then I can sort on the formula value.

Any help would be greatly appreciated.
Question by:chamlight
  • 2
  • 2
LVL 100

Accepted Solution

mlmcc earned 250 total points
ID: 35172306
Try this idea

Create a formula

Another way so you can keep the R

Left({itemCodeField},1) & CStr(Val(Mid({itemCodeField},2), "00000")



Author Comment

ID: 35172430

Wow, that was fast.  Thanks, I'll try them.  BTW can you see any problems I might have with the following?

toNumber (replace ({chaProducts.itemCode},"R","",1,1,1))
LVL 100

Expert Comment

ID: 35172465
That should work as well


Author Closing Comment

ID: 35172477
Thanks.  Your solution is more...elegant

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question