[AccessD] values separated by .

Pedro,
I think the following may be of help to you.

It's a response from
/gustav
to a question of mine involving splitting a string containing several dots

HTH

Regards
Borge

Here it is:

**************
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
************************

----- Original Message ----- 
From: "Pedro Janssen" <pedro at plex.nl>
To: <AccessD at databaseadvisors.com>
Sent: Friday, February 18, 2005 11:00 PM
Subject: [AccessD] values separated by .


Hello group,

I have a field [formula] with values that look like:  6.11.3.1.2(0m).1.0.1
Each value is separated by a dot  (.)
As you can see, not al values are numbers.

I would like each value in a separated field. FieldA to FieldH.

I could do this with Left, Right or Middle function, but it takes time an 8
functions

Is there a better way to do this in one.

Pedro Janssen
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com