Ordering Text Fields with Multiple Decimal Points?

[Bill]

Hi All,

I have a text field with data like this.

2.1
2.2
2.2.1
2.2.2
2.3
2.9.3
2.9.4
2.9.5
2.10
2.10.1

I need to sort other fields in the query but maintain this order in the
first column, but of course a sort on a text field puts the 2.10 immediately
after the 2.1 etc

VAL doesn't work because it doesn't account for the second decimal point.

Any ideas how I maintain the order I need please.

Thanks.
Bill.

 

[Allen Browne]

The problem here is that the data is not atomic, i.e. you have multiple
pieces of data in one field.

A workaround might be to write a function to Split() the number at the dots,
and insert leading zeros. For 2.1.10, the function would return 02.01.10 (or
perhaps 002.001.010 if you need 3-digit segments.) Use the function in the
ORDER BY clause of your query, and it should sort correctly.

A better solution might be to use 3 separate fields of type Number, so you
can sort by the 3 fields.

 

[Marshall Barton]

I have a text field with data like this.

2.1
2.2
2.2.1
2.2.2
2.3
2.9.3
2.9.4
2.9.5
2.10
2.10.1

I need to sort other fields in the query but maintain this order in the
first column, but of course a sort on a text field puts the 2.10 immediately
after the 2.1 etc

You need to sort on a uniform pattern something like 002.009
to make 2.9 sort before 2.10

It may not be very quick, but you can do that by creating a
function similar to this in a standard module:

Public Function MyNum(s As Variant) As Variant
Dim x As Integer, y As Integer

If IsNull(s) Then Exit Function

y = InStr(s, ".")
Do Until y = 0
MyNum = MyNum & Right("000" & Mid(s, x + 1, y - x -
1), 3)
x = y
y = InStr(x + 1, s, ".")
Loop
MyNum = MyNum & Right("000" & Mid(s, x + 1), 3)
End Function

Then your query or report can sort on MyNum([the field])

 

[Bill]

Ok, thanks for the suggestions.
I will have a play.
Regards.
Bill.

 

[Bill]

Marshall, that worked perfectly.
Many thanks.
Bill.

 

[Marshall Barton]

Marshall, that worked perfectly.
Many thanks.
Bill.

If you find that it takes too long, try Allen's workaround
using Split instead of my InStr parsing.

 

[Bill]

If you find that it takes too long, try Allen's workaround
using Split instead of my InStr parsing.

The speed is fine. The table that it applies to only has 91 records.