sorting text as number

[Steve]

Got trouble. Have a field that stores text as numbers.
Like this:

3.1.1
3.1.2
3.1.3
3.10.1
3.10.10
3.10.11
3.10.12
3.10.13
3.11.25
3.11.26
3.11.3
3.11.4
3.11.5
3.11.6
3.11.7
3.11.8
3.11.9
3.2.1
3.2.2

Any idea how to sort these numbers numerically so that
after 3.1.1 will be 3.2.1???? The sort gets all messed
up.

Thanks,

 

[Douglas J. Steele]

The only ways I can think of is to store them as 3 separate numbers, and
sort those numbers, or store the text with preceding zeros: 3.01.01, rather
than 3.1.1

If you only have two levels (3.1), you could convert them to decimal values,
but that's not an option given you've got 3 levels.

 

[PC Datasheet]

From one Steve to another Steve ---

Put the following expression in a query and sort on that field:
Val([textfieldname])


Steve

 

[Douglas J. Steele]

As I pointed out in my reply, that won't work, since there are 3 levels of
numbers.

Val("3.1.1") = Val("3.1.2") = Val("3.1.3") = 3.1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

From one Steve to another Steve ---

Put the following expression in a query and sort on that field:
Val([textfieldname])


Steve


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Got trouble. Have a field that stores text as numbers.
Like this:

3.1.1
3.1.2
3.1.3
3.10.1
3.10.10
3.10.11
3.10.12
3.10.13
3.11.25
3.11.26
3.11.3
3.11.4
3.11.5
3.11.6
3.11.7
3.11.8
3.11.9
3.2.1
3.2.2

Any idea how to sort these numbers numerically so that
after 3.1.1 will be 3.2.1???? The sort gets all messed
up.

Thanks,

 

[Van T. Dinh]

Not tested but you can try this:

In the QueryGrid, create 3 Calculalted Fields / Columns using the Split
function as follows (left to right):

Num1: CInt(Split([YourField], ".", 3)(0))
Num2: CInt(Split([YourField], ".", 3)(1))
Num3: CInt(Split([YourField], ".", 3)(2))

Set "Ascending Sort" for each of the above Calculated Column.

HTH
Van T. Dinh
MVP (Access)

 

[Jamie Collins]

The only ways I can think of is to store them as 3 separate numbers, and
sort those numbers, or store the text with preceding zeros: 3.01.01, rather
than 3.1.1

Or store as text and split they need to be sorted e.g.

SELECT MyTextCol
FROM MyTable
ORDER BY
CLNG(MID(MyTextCol, 1, INSTR(1,MyTextCol,".")-1)),
CLNG(MID(MyTextCol, INSTR(1,MyTextCol,".")+1,
INSTR(3,MyTextCol,".")-1)),
CLNG(MID(MyTextCol, INSTR(3,MyTextCol,".")+1))
;

However, I'd go with the 'store them as 3 separate numbers' suggestion
with an appropriate CHECK constraint.

Jamie.

--