Odd Sorting Behavior?

[Vita]

So I have my sorting for a report setup as Length with A on top (descending) by entire value with a sub sort of Quantity with A on top by entire value.

This is the data I am putting in

Length	Quantity
10	200
5	850
5	430
10	560
10	440
6	180
10	60

But the order It sorts into is

Length	Quantity
10	200
10	440
10	560
10	60
5	430
5	850
6	180

For some reason in the sub sort(quantity) the 10 x 60 comes at the end and in the main sort(length) the 6 comes after the 5's.
Does anyone know why it acts like this? It doesn't make any sense? 6 it higher than 5 haha.

Also sometimes length will be something like 50" in which case I want it to be at the bottom of the sort or Quantity will be in something like 35 PCS and I would want that to go on the bottom of its length sort.
I am not sure how to do this through the sorting menu though. Is there perhaps a way I can sort them based on some expressions that decide where they should go? (I read on another forum post that reports only sort by the sorting/grouping ui and I am hoping there is a way around that.)

This is what I would ideally like the results to be

Length	Quantity
10	60
10	200
10	440
10	560
10	35 PCS
6	180
6	10 PCS
5	430
5	850
56"	180
45"	25 PCS

I hope the example tables help. Thanks for any help!

[Micron]

No doubt your values are strings (text data type). In text, 560 comes before 60. Best not to use text data type for numbers that you want to sort on or do math with. That means getting rid of " and PCS and adding fields for units of measure and quantity.

[Vita]

No doubt your values are strings (text data type). In text, 560 comes before 60. Best not to use text data type for numbers that you want to sort on or do math with. That means getting rid of " and PCS and adding fields for units of measure and quantity.

Why does 560 come before 60? Shouldn't it come after since it is in the hundreds? I would understand that if it was sorting by the first 2 characters but I told it to sort by the entire value.

Is there anyway that I can write a custom sort in vba?

[Minty]

As text it has no concept of the number value of the text, it's simply a string of characters.
Consider these values as sorted alphabetically.

Was
We
Where

Just because "Where" is longer than "We" you wouldn't expect to appear before "We", would you?

Similarly strings of numbers sorted ascending behave like this:

String_id
1
10
11
12
13
14
15
16
17
18
19
2
20
21
22
23
24
25
26
27
3
4
5
6
7
8
9

[Gicu]

Do you want the inches to come before the feet?
You could add a calculated field to the report's record source and use that for the sort order in the report design:

SortOrder: IIf(InStr([Quantity],Chr(34))>0,CDbl("0." & Val([Quantity])),CDbl([Quantity]))

Cheers,

[Micron]

Why does 560 come before 60?

Because 5 comes before 6, the rest doesn't matter. Your data behaves like alpha because the values are strings (text data type). Think of it as being like a directory of telephone listings - strictly alphabetical. As I said, if you want numerical sorting you have to ditch the text characters like " and PCS and change the field data type to one of the number type options you have.

EDIT

the rest doesn't matter

well, it does but only if you think of it in alphabetical terms.

[Vita]

Do you want the inches to come before the feet?
You could add a calculated field to the report's record source and use that for the sort order in the report design:

SortOrder: IIf(InStr([Quantity],Chr(34))>0,CDbl("0." & Val([Quantity])),CDbl([Quantity]))

Cheers,

This did exactly what I wanted. Well I changed it to Lengths and then wrote one that searches for "PCS" in quantity and put it underneath.
Thanks! I was unaware Val was a function.