Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2004
    Posts
    39

    Unanswered: Split command in query

    hi everyone,
    i want to have a query which will split the data in one column into different columns. i tried with instr function, but its a pain.
    i have the data as follows (sample of 5 rows)

    partDesc
    ----------
    COMPAQ, DP EN, MT, PII, 350, 64MB, 4.0GB, CD
    COMPAQ,ARMADA 1590DMT, PENTIUM, 166MMX, 80MB, 2.0GB, ACT, 12.1, CD, COMPAQ,ARMADA 7792DMT, PENTIUM, 266MMX, 144MB, 5.0GB, ACT, 13.3, COMPAQ,ARMADA 1750, PII, 366, 128MB, 6.0GB, ACT, 13.3, CD, MODEM O/B, DELL,PRECISION 410, MT, PIII, 450, 128MB, 9.0GB, CD, ENET O/B TOKEN RING

    I want the 6th value, meaning the value after the 5th comma(",").
    how can i do this.
    and everything i need to do in a query and no code.
    thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by madhavi28
    and everything i need to do in a query and no code.
    thanks.
    why? What sort of field looks like this? That's hideous. Is this an import thing you're working on? What prevents you from using code on an import script?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Posts
    39
    hello,
    if u can help, try to help or else ignore.ok.
    the way i pasted the data, and the way it is displaying is different.ok.
    all data is from the table only, nothing imported
    and i cant use any code.thats it.
    just try to tell, how to retrieve data which is after the occurence of the fifth comma.
    if u can give me the code, and i'll use it somehow.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's the difference between you asking me for free consulting services and me helping you understand why your schema is bad. If you would like help straightening out your schema so you can avoid situations like you're in, this is the right place.

    If would like someone to "just give you the code", then you may contact me at my listed email and we can discuss rates.

    PS: I have already posted vba code to do this today.
    Last edited by Teddy; 09-22-04 at 15:16.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I don't think you can do it only with queries. data fields are not even. it is obivious you need some VBA code.
    ghozy.

  6. #6
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    That's the difference between you asking me for free consulting services and me helping you understand why your schema is bad. If you would like help straightening out your schema so you can avoid situations like you're in, this is the right place.

    If would like someone to "just give you the code", then you may contact me at my listed email and we can discuss rates.










    <snicker>

  7. #7
    Join Date
    Jun 2004
    Posts
    39
    thanks for ur suggestions. i could figure it out.

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    It's possible to do using queries. But IMO you must use Instr, like it or not.

    Create 6 queries each one based on the previous. Don't try and do it in one or you'll end up in a mental home.

    My table is called myData with field PartDesc

    1st Query: It1
    SELECT Right([PartDesc],Len([PartDesc])-InStr([PartDesc],",")) AS PartDesc1
    FROM myData;

    2nd Query: It2
    SELECT Right([PartDesc1],Len([PartDesc1])-InStr([PartDesc1],",")) AS PartDesc2
    FROM It1;

    There's a pattern forming here....

    5th query: It5
    SELECT Right([PartDesc4],Len([PartDesc4])-InStr([PartDesc4],",")) AS PartDesc5
    FROM It4;

    6th query (giving the result): It6
    SELECT Trim(Left([PartDesc5],InStr([PartDesc5],",")-1)) AS Word6
    FROM It5;

    HTH
    Chris

  9. #9
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I managed to do it in one query but it ain't pretty

    Code:
    Expr1: Trim(Mid([PartDesc],InStr(InStr(InStr(InStr(InStr([PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")+1,InStr(InStr(InStr(InStr(InStr(InStr([PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")+1,[Partdesc],",")-InStr(InStr(InStr(InStr(InStr([PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")+1,[PartDesc],",")-1))
    Ok I used Instr a bit, but I didn't use code.

    Chris

  10. #10
    Join Date
    Jun 2004
    Posts
    39
    thank you very much Chris.
    You are really wonderful. Thanks for the help.
    The query you sent me works soooooooo fine that i can't express.
    Thanks a lot.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hey awesome! Do you understand what it does?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Jun 2004
    Posts
    39
    may be you can't understand, but i can.
    try to understand.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Insults don't have quite the same bite when written in Engrish.

    Just sayin...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    Quote Originally Posted by madhavi28 View Post
    hi everyone,
    i want to have a query which will split the data in one column into different columns. i tried with instr function, but its a pain.
    i have the data as follows (sample of 5 rows)

    partDesc
    ----------
    COMPAQ, DP EN, MT, PII, 350, 64MB, 4.0GB, CD
    COMPAQ,ARMADA 1590DMT, PENTIUM, 166MMX, 80MB, 2.0GB, ACT, 12.1, CD, COMPAQ,ARMADA 7792DMT, PENTIUM, 266MMX, 144MB, 5.0GB, ACT, 13.3, COMPAQ,ARMADA 1750, PII, 366, 128MB, 6.0GB, ACT, 13.3, CD, MODEM O/B, DELL,PRECISION 410, MT, PIII, 450, 128MB, 9.0GB, CD, ENET O/B TOKEN RING

    I want the 6th value, meaning the value after the 5th comma(",").
    how can i do this.
    and everything i need to do in a query and no code.
    thanks.
    I know this post is way old, but just for posterity's sake, here is a function I typically use for this type of thing:

    Code:
    '------------------------------------------------------------------------------------------------
    ' Copyright 2012 by Kael Dowdy.  All Rights Reserved.
    '
    ' Copyright: This code was originally written by Kael Dowdy.  It is not to be altered or
    '            distributed except as part of an application.  You are free to use this code
    '            provided this copywright notice is left unchanged.
    '
    ' Function:  GetSegmentValue()
    ' Purpose:   Return segment value from a multi-valued string
    ' Author:    Kael Dowdy / kdowdy(at)kellerschroeder(dot)com / Keller Schroeder & Associates, Inc.
    '
    ' Inputs:    SegmentString - delimited string containing the segments you want to parse
    '
    '            SegmentDelimiter - character(s) which separate the various segments in the
    '            SegmentString parameter (Note: you don't need to provide spaces)
    '
    '            SegmentNumber - position of segment you wish to return (Note: segment position 1
    '            would be a 1 and not a 0 -- even though arrays are 0 based)
    '
    '            SegmentEmptyMessage (Optional) - if the segemnt value is blank/empty and you want to
    '            return a specific value other than an empty string, initialize this parameter with
    '            the value you want to return instead
    '
    '            SegmentNotFoundMessage (Optional) - if the requested segment number was not found
    '            and you want to return a specific value other than an empty string, initialize this
    '            parameter with the value you want to return instead
    '
    ' Output:   Segment value for the segment number/position you want to return
    '------------------------------------------------------------------------------------------------
    
    Public Function GetSegmentValue(SegmentString As String, SegmentDelimiter As String, SegmentNumber As Long, Optional SegmentEmptyMessage As String, Optional SegmentNotFoundMessage As String) As String
        On Error GoTo ErrTrap
        
        Dim SegmentArray As Variant, SegmentValue As String
        
        SegmentArray = Split(SegmentString, SegmentDelimiter) ' place segment into array, splitting on delimiter
    
        SegmentValue = Nz(Trim(SegmentArray(SegmentNumber - 1)), "") ' arrays are 0 based, so we subtract one from the requested segment number value
    
        ' check if user wants an empty segment message
        If SegmentValue = "" And SegmentEmptyMessage <> "" Then
            SegmentValue = SegmentEmptyMessage
        End If
        
        GetSegmentValue = SegmentValue ' return segment value
        
        Exit Function
    ErrTrap:
        Select Case Err.Number
            Case 9 ' segment position not found
                ' check if user wants a segment not found message
                If SegmentNotFoundMessage <> "" Then
                    GetSegmentValue = SegmentNotFoundMessage
                End If
        End Select
    End Function
    Just save this function in a new module, and then in the query, create a field expression that makes a call to this public function.

    Ex: PartDescription: GetSegmentValue([partDesc], ",", 6)

    This approach may be slightly slower than using a plethora of intrinsic functions directly in a query (such as InStr, Len, Right, Left, etc). However, the advantage of using this function is flexibility for multiple situations without any regard to how to actually implement the code. Just pass in the string to parse, the delimiter, and the segment position you want to return, and you're all set!

Posting Permissions

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