How can I put a numeric field in order when...

[QueSpr04]

How can I put a numeric field in order when it is a alphanumeric number. For instance, the scenerio is a case when the numbers go in order but it takes the number 102 and places it in front of the numbers preceeding with zeros; ex. (mv01, mv04, mv05, mv07, mv102, mv14) How can I place the 102 at the very end where it belongs? Any suggestions?

 

[Turkbear]

I am not sure you can..Alpha values will sort that way.

You could try substringing the field to just get the numeric part, use the To_Number function to change it to a number and sort by that ( make it a group on that formula, hide/supress it and place the actual value in the details, maybe)

 

[lbass]

If the number always starts in the third position, then I think you could create a formula:

val(mid({table.field},3))

...and then sort on this formula. This assumes that the left two characters are always the same. If they can be different, and you want to sort by them as well, then try the following formula as a sort formula:

left({table.field},2)+totext(val(mid({table.field},3)),"000")


-LB

 

[QueSpr04]

lbass,

That particular formula doesn't work because it truncates the alphabets and it also still puts the 102 between the 0's and the 1's.
Thanx though! I've saved that formula for later use!

 

[lbass]

Please explain. Are you saying that the lefthand part of the field can be more than two characters?

This formula should sort correctly, since it would force 01 to appear as "001", "14" as "014", and "102" as it is. Did you copy the formula exactly as above and then use if for the sort field? It does change the right hand part of the formula so that it will sort correctly--but this formula is for sorting ONLY. Go to report->sort records and insert it there. You would still use your regular table field for display purposes.

-LB