Sorting Text Fields as numbers

  • Thread starter Thread starter Hydra
  • Start date Start date
H

Hydra

How do yu force a text field to sort as numbers, for example WBS numbers
where you have :
1.1.8
1.1.9
1.1.10
1.1.11

I know you can add the leading zeros, but the data comes from another
source.....
 
Hi Hydra,
you can add a field in a query that look like this

CLng(replace([WBS];".";""))

and sort on it

HTH Paolo
 
I think you would need a custom vba function to create a sort field and
use the result of that. Something like the following UNTESTED VBA
function should work for your specific example.

Public Function fSpecialSort(strIN)
'Pad WBS numbers
Dim sReturn as Variant
Dim sArray as Variant
Dim iPos as Long

IF Len(strIn & "") = 0 then
sReturn = strIn
ELSE
sArray = Split(strIn,"."
For iPos = LBound(sArray) to UBound(sArray)
sReturn = sReturn & Right("00000" & sArray(iPos),5)
Next iPos
END IF

fSpecialSort = sReturn
End Function

Of course, that could be made much more generic by allowing you to
specify the separator, the padding character, and length of the segments
as arguments to the function.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John's solution is generic, but if you have only two dots, and no number
greater than 99, you can sort on the computed expression:

eval( "100*" & Replace( WBS, ".", "*100+"))


Vanderghast, Access MVP
 
Paolo,

I think that will cause problems. For instance, you would end up
sorting 1.1.8 , 1.18, and 11.8 together before 1.1.9, 1.19, and 11.9.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi Hydra,
you can add a field in a query that look like this

CLng(replace([WBS];".";""))

and sort on it

HTH Paolo

Hydra said:
How do yu force a text field to sort as numbers, for example WBS numbers
where you have :
1.1.8
1.1.9
1.1.10
1.1.11

I know you can add the leading zeros, but the data comes from another
source.....
Click to expand...
Click to expand...
 
Yeah John, I know that, but actually I don't know what a WBS number is and
the values it can assume, so I answered for the example Hydra exposed.

Cheers Paolo

John Spencer said:
Paolo,

I think that will cause problems. For instance, you would end up
sorting 1.1.8 , 1.18, and 11.8 together before 1.1.9, 1.19, and 11.9.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi Hydra,
you can add a field in a query that look like this

CLng(replace([WBS];".";""))

and sort on it

HTH Paolo

Hydra said:
How do yu force a text field to sort as numbers, for example WBS numbers
where you have :
1.1.8
1.1.9
1.1.10
1.1.11

I know you can add the leading zeros, but the data comes from another
source.....
Click to expand...
Click to expand...
Click to expand...
 
vanderghast wrote:
| John's solution is generic, but if you have only two dots, and no
| number greater than 99, you can sort on the computed expression:
|
| eval( "100*" & Replace( WBS, ".", "*100+"))


Order by
CDate(Replace(WBS,".",":"))

while first number <24 and the other numbers are <60

;-)
 
Back
Top