asked on

Sorting Records in Crystal Reports

Hi,

I have an issue with sorting records alpha numerically in Crystal. I'm not sure it's Crystal as much as the way we have coded our back end MSSQL database.

We have reference numbers which are like this:

AA1
AA2
AA3
AA4
AA5
AA6
AA7
AA8
AA9
AA10
AA11
AA12 - and so on...

What is happening in sorting my records is this:

AA1
AA10
AA11
AA12
AA2
AA3
AA4 and so on...

Is there a way to sort these in proper alpha numerical order even if a formula has to be used? It's creating a nightmare with my reports.

Any help would be much appreciated.

Best Regards,

Ken

AA11

peter57r

Do all values start with two letters?
What is the longest number?
Assuming 2 letters followed by up to 4 digits then...

Left({table.field},2}) & right("0000" & mid({table.field},3), 4)

kenuk110

ASKER

Hi,

We used a spreadsheet type coding system so to answer your question, no, they can be like this:

A1
A10
AA1
AA10

I know I know, it's a stupid way to code things! they are never more than 2 aplha and 2 numeric though.

Is it still possible to do this with what you have suggested?

Cheers

kenuk110

ASKER

I tried to input the suggested code but I'm not sure where to put it, I went in to Group Expert then selectd the field that I want to sort, selected 'group by formula' but it won't let me select the table.field I need to use. Am I going to the wrong place?

peter57r

It'll be a bit more involved then.
You have to create a new formula field. You can't adjust what's already there.
Then sort on the formula field.

If {table.field}[2] in ("0" to "9") then
Left({table.field},1}) & right("00" & mid({table.field},2), 2)
else
Left({table.field},2}) & right("00" & mid({table.field},3), 2)

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial