Crystal Report sort problem, need to strip out data and convert to num 1

[siboyd07]

I have a text field (Card_no) that sometimes contains numbers and it sometimes contains both numbers and letters. Example:
500-10-2
500-1-2
hec-1
hec-1-a

When the fields contains numbers they need to be sorted as
500-1-2
500-10-2 because 1 comes before 10. I'm having a problem figuring out a way to sort the data that won't throw an error when the field doesn't contain all numeric data.

Does anyone have any suggestions?

 

[IanWaterman]

YOu could try using 2 formula, assuming delimiter is always -.

@pt1
split(yourfield, "-")[1]

@pt2
split(yourfield, "-")[2]

Then sort in order on these formula. Bearing in mind I think Crystal sorts alphas before numbers.

Ian

 

[siboyd07]

This worked well for the 1st part but if there is no 2nd dash I'm getting the error

"A subscript must be between 1 and the size of the array"

The problem is the number of dashes is not always the same. Sometimes the card_no could be 500-1-1, 500-10, 500, hec-10-1.

I basically need to look at each section and build a sort level based on each level, regardless of the number of dashes. I think the most I there will be is 2.

 

[fisheromacse]

i think something like this should check to make sure there is a 2nd value before trying to do anything with it.



@pt2
if ubound(split(yourfield, "-"))>1 then split(yourfield, "-")[2] else

 

[siboyd07]

That worked!

Thanks so much. I've been working on this all day.