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

Gustav,

Thanks much.

Tried it out and it works perfectly!!!

Yesterday, I did something similar in the sense of creating three fields to
sort on.
However, a very crude solution based on some string manipulation code that
separates the words in a delimeted string, using "." as the delimter. I
created separate functions for the up to three "words" I knew could be in
the string. Using these three functions in the sql code, I created
FirstSort, SecondSort and ThirdSort. I then found that the query cannot be
sorted. Further, saving the query and then linking the query to the table to
be sorted didn't work either: unable to use the three fields to sort on. So
I ended up adding the three redundant sort fields to the table, and applying
the sql as an update query. As I said... crude

Regards
Borge

----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Wednesday, June 18, 2003 1:13 AM
Subject: Re: [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
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com