Sorting of numeric data in text column

M

M Vally

how do I get a numric sequence of numbers in a text
column if numbers are like this 1-001,1-002,2-001,10-001
at the moment I get 1-001,1-002,10-001,2-001

Thaks in advance
 
R

Rick Brandt

M Vally said:
how do I get a numric sequence of numbers in a text
column if numbers are like this 1-001,1-002,2-001,10-001
at the moment I get 1-001,1-002,10-001,2-001
Click to expand...

Well, those values are not numbers as numbers don't contain dashes. In a
query you could sort on an expression...

SortValue: Val([FieldName])

That would return the numeric value up to the dash. You might need an
additional expression to sort on everything after the dash...

SortValue2: Val(Mid([FieldName], InStr(1, [FieldName], "-")+1))

That should return the numeric value represented by the digits after the
dash. The two could be combined into a single field to sort on if desired.
 
Top