Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    23

    Unanswered: Query Help: Divide 1 Field into several fields

    I have a field in a table that supplies 5 groups of numbers seperated each seperated by a blank space:

    TrainerRecordCY
    46 6 8 5 -1.01
    2 1 0 0 -0.20

    Does anyone know what formula I should use in a query or does this have to be done usein code. Could anyone supply either one?

    Thanks
    Jim

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you could get there with a spaghetti-mess of left$(), len(), mid$() and InStr() but it is going to be a nightmare. in code you could also use split()

    presuming that you HATE the table design that gives you five lumps of data in one field and that this is a one-time effort to fix the mess...

    how about:
    dump the table out to .txt
    and reimport space-delimited ??

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Just to give you some scope about what izy is talking about,
    here's what it takes to extract the first 2 numbers:
    Code:
    SELECT Mid$([tr],1,InStr(1,[tr]," ")) AS Expr1, 
    Mid$([tr],InStr(InStr(1,[tr]," "),[tr]," "),InStr(InStr(1,[tr]," "),[tr]," ")) AS Expr2
    FROM Table1;
    where tr is your field name.

    This would continue to get worse as you worked your way across.
    I now have a splitting headache, Thank You Very Much!
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •