specialized sort

[Guest]

Hi,

I have a DB with a column that needs to be sorted as follows:
00
01
04
08
1
1
1
4
8
9
10
11
20
24
2K
2K4
30
300
99
Y12

The column is defined as text. How do I get it to sort as above?

TIA,
Dave

 

[Albert D.Kallal]

Just build a table with two columns

MyTextValue MySortOrder
00 10
01 20
04 30
08 40
1 50

etc. etc.
The first column above would be a text type field..and the 2nd would be a
number type field.

Now, just build a query with your original table. Then throw in the above
table..and join it to the text value (use a left join). Now, just drop in
the mysortoder field..and sort on that. You can use this for reports...or
whatever. I also number things by 10, just in case you need to later add a
few new failures that go between.

 

[Ed Warren]

You have mixed concepts of text and numbers and you are asking access to
know when to consider the field a number and when to consider the field
text.

10 and 11 are numbers (follow 9 not grouped with the 1xxx as text)
yet 300 is text (preceeds 99).

The only way I could do this would be via VBA code, and would have to know
all of the many variations of the data to accomplish that.

The function below will provide a string that can be used to sort on, and
will produce the sort you requested for this set of values, note it is not
error trapped for passing a null string and it may or may not work for all
cases you may have, it should be considered a starting point.

Public Function getSort(strPassed As String) As String
Dim intLength As Integer
Dim chrFirst As String
chrFirst = Left(strPassed, 1)
If chrFirst = "0" Or Len(strPassed) = 1 Then
getSort = strPassed
Else
getSort = "a" & strPassed
End If

End Function

Query:

SELECT Table1.text
FROM Table1
ORDER BY getsort([text]);

Ed Warren.