Sort numbers in found set

[jrRaid]

I have a found set of numbers going from 1 to 70.

Problem is that in taht range (2 digits), there are numbers with 3 digits.

The 'sub-numbers', who are also in the regular number fields.

Sorting on the numbers will put the sub-numbers (like 610, 620, 650 etc) at the beginning or the end of the sorted range, where I need them (in the above example), between 60 and 70.

The range should be:

60

610

620

650

70

...

any hint how, without creating too much 'in between fields' ?

TIA

[Ender]

I'd suggest putting "sub-numbers" in a separate field. You could have a calc field (or merge field) that combines them for display purposes, although I think it's clearer if they're kept separate.

[Raybaudi]

Hi

can you live with "numbers" that are not numbers, but text ?

Because, if those "numbers" are stored as text, sorting gives you what you wish.

[jrRaid]

Ok Ender, thanks.

I did that, a new field,moved the 3 digit nrs to that field, and replaced the 3 digit by the corresponding 1 or 2 digit.

Still when sorting, it doesn't match. Although I'm already closer.

60

609

610

611

62

is what it should be...

60

62

609

610

611

is what it is.

What do I miss here ?

Somebody suggested to change the numbers to text...but I don't think that is a solution...

[Lee Smith]

Is your field type = Number?

If it is text, it will sort the way you are describing.

If is a number, then it will sort properly.

HTH

Lee

[Raybaudi]

Hi Lee

this type of sorting:

60

610

620

650

70

can be obtained only if the field is text type

[Lee Smith]

I read the "what it is" as "what it should be".

[jrRaid]

I duplicated the number fields, made them calculation = original number field, result text and sorted ....

It works... thanks everybody...great help.

Guess I will follow 'all' the proposed solutions, even when for me they don't sound very 'logic'...

Edited January 4, 2007 by Guest