I've got a DB with area Version. There can be many records with similar version string, to take out versions I've, I actually do the next:

SELECT DISTINCT tblSample.Version
FROM tblSample
ORDER BY tblSample.Version DESC;

Here's sample data:


Things I get after performing query:


What I wish to get is(because 15 is clearly > 2 and 1):


Correspondingly other octets should be thought about too.

What you're attempting to do is similar to sorting IP addresses. You are able to stick to the identical techniques layed out for the reason that article to complete what you're after.

Essentially if you wish to sort on x and perform a distinct on y you must do the distinct first (hence the subquery)

While using following function (modified from fredg's answer in this thread) Note: the function uses the split function that we think is suitable here.

Public Function ParseText(TextIn As String, X) As Variant
    On Error Resume Next
    Dim var As Variant

    var = Split(TextIn, ".", -1)
    ParseText = var(X)

End Function

You are able to write this SQL Statement

SELECT  t.Version
    ( SELECT Distinct tblSample.Version
      FROM tblSample.Version) t
    Cint(ParseText([t.Version],0)) DESC , 
    Cint(ParseText([t.Version],1)) DESC , 
    Cint(ParseText([t.Version],2)) DESC;

Upate As HansUp noted. You should also cast for an int to obtain the sorting properly. You may either do that within the SQL (as my up-to-date answer has) or this can be done in ParseText and return an int

Store Version as 3 number fields.

major minor release
    1     1    1000
    1     2    1654
    1    15    1223
    1     2    1654
    1     1    1000

You'll be able to generate Version, sorted as you want, having a query whenever you really need it.

SELECT [major] & "." & [minor] & "." & [release] AS Version_num
FROM [SELECT DISTINCT major, minor, release
FROM tblSample]. AS s
ORDER BY s.major DESC , s.minor DESC , s.release DESC;

One solution could be: parse the strings that you will get which means you extract the statistical values, place leading 0s in-front when needed, and output a string. E.g. 1.2.1654 becomes 0001.0002.1654 (if you are sure the numbering will not exceed 4 figures). Code:

Function NormalizeVersion(Inputstring As String) As String
' Creates sortable strings out of version numbers such as 1.6.222
Dim Elements() As String
Dim Counter As Integer
Dim Result As String
Elements = Split(Inputstring, ".")
For Counter = 0 To UBound(Elements)
    Select Case Counter
    Case 0 'First element
        Result = Format(Elements(Counter), "00000")
    Case Else 'Followups
        Result = Result & "." & Right("0000" & Elements(Counter), 5)
    End Select
Next Counter
NormalizeVersion = Result
End Function

(incorporated HansUp's performance improvement) You'll be able to sort about this.

Write a VBA function that performs this for you personally, known as e.g. NormalizeVersion, then refer to it as inside your query, for example (air code):

SELECT DISTINCT NormalizeVersion(tblSample.Version)
FROM tblSample
ORDER BY NormalizeVersion(tblSample.Version) DESC;

(code fixed after comment)