Crystal Report Record Sorting - Numerical?

[Former Member]

I have a report I'm trying to sort by a certain field. This field generally consists of numbers only, two digits max.

I have it sorted, ascending. Here is what I get:

1

10

11

12

13

14

2

3

4

4a

4b

5

6

7

8

9

No I would just convert that field to a numerical value, but as you can see it may contain letters. Any ideas?

[Former Member]

Works perfect, thanks guys!

[Former Member]

I got this formula to work as well

IF IsNumeric({TableName.TextNum}) = False
THEN "0" & {TableName.TextNum} ELSE
IF Len({TableName.TextNum}) < 2
THEN "0" & {TableName.TextNum} ELSE
{TableName.TextNum}

Which created the following sort order...

1

2

3

4

4a

4b

5

6

7

8

9

10

11

12

13

14

HTH,

Jason

[DellSC]

This is going to be a little tricky in order to get the data that includes alpha characters to sort correctly, but it can be done.

Try something like this:

if length({table.field}) = 1 then
  "0" + {table.field})
else if NumericText(right({table.field}, 1) then {table.field}
else "0" + {table.field}

This formula looks to see if the length of the data in the field is 1 and if it is, appends a zero to the beginning. If the length of the field is greater than one, it looks to see if the last character in the field is a number and if it is, use just the field otherwise append a zero to the beginning to make a 3-character result that will sort correctly with the other numbers.

If the data is ever more than two characters (which you say it isn't) then this formula will have to be modified to account for the additional character positions.

-Dell