[AccessD] Text Field Sort Order 1.1 - 10 - 2 into 1.1 - 2 - 10

Hi Børge

I found something from an old similar conversion routine.
It's a query which cannot be sorted, so feed the output to another
query where you select the needed fields only and order by Fld1, Fld2,
Fld3.

<SQL>

  SELECT
    *,
    InStr(1,[StringID],".") AS Dot1,
    Val(IIf([Dot1]=0,
      [StringID],
      IIf([Dot1]=1,
        "",
        Left([StringID],[Dot1]-1)))) AS Fld1,
    IIf([Dot1]=0,
      "",
      Mid([StringID],1+[Dot1])) AS FldRest1,
    InStr(1,[FldRest1],".") AS Dot2,
    Val(IIf([Dot2]=0,
      "" & [FldRest1],
      Left([FldRest1],[Dot2]-1))) AS Fld2,
    IIf([Dot2]=0,
      "",
      Mid([FldRest1],1+[Dot2])) AS FldRest2,
    InStr(1,[FldRest2],".") AS Dot3,
    Val(IIf([Dot3]=0,
      "" & [FldRest2],
      Left([FldRest2],[Dot3]-1))) AS Fld3
  FROM
    tblYourTable;

</SQL>

It will also handle entries like

  .8.70

and

  ..61

/gustav


> We have a text field with data like this when sorted ascending:

> 1
> 1.1
> 1.2
> 10
> 10.1
> 11
> 11.1
> 11.2
> 2
> 2.1
> 2.2
> 3
> 3.1.
> 3.1.1
> 3.1.2
> 3.1.3
> 4
> etc etc.

> Any one got ideas for how to sort the field like this:
> 1
> 1.1
> 1.2
> 2
> 2.1
> 2.2
> 3
> 3.1.
> 3.1.1
> 3.1.2
> 3.1.3
> 4
> ....

> 10
> 10.1
> 11
> 11.1
> 11.2

> ?????

> Regards
> Borge Hansen