Results 1 to 7 of 7

Text to Columns in access ???????

This is a discussion on Text to Columns in access ??????? within the Microsoft Access forums, part of the Question Forums category; I need help duplicating "text to columns" in access. I have a database field that is 6 characters log. I ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Minneapolis, MN
    Posts
    148

    Exclamation Text to Columns in access ???????

    I need help duplicating "text to columns" in access.

    I have a database field that is 6 characters log. I need the field broken down into 3 columns depending on where the hyphen is.

    I tried using Left & Right, but the problem is that in some cases the entire 6 characters are used up, and not in others, but the extra character space is still there.

    Example:
    Shifts
    12-2-1
    3-1-2

    Is there some sort of VBA code that can do the breakouts based on teh hyphen vs character count?


    Thanks,
    David

  2. #2
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: Text to Columns in access ???????

    Just use

    For the first one:

    Left([FieldName], Instr(1, [FieldName], "-") -1)


    and for the second:

    Mid([FieldNameHere], InStr(1, [FieldNameHere], "-") + 1, Len([FieldNameHere]) - InStrRev([FieldNameHere], "-"))

    And for the last

    Right([FieldNameHere], Len([FieldNameHere]) - InStrRev([FieldNameHere], "-"))
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


  3. #3
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: Text to Columns in access ???????

    Sorry, the MID isn't working for me. So I'm going to do it another way.
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


  4. #4
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: Text to Columns in access ???????

    Put this function in a standard module and then call it as you wish:
    Code:
    Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As String
        SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1)
    End Function
    In the query you can pass the value like:

    Code:
    SELECT SplitField([YourFieldNameHere],"-",1) AS TheLeft, SplitField([YourFieldNameHere],"-",2) AS TheMiddle, SplitField([YourFieldNameHere],"-",3) AS TheRight
    FROM Table4;
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Minneapolis, MN
    Posts
    148

    Default Re: Text to Columns in access ???????

    Bob,
    Thanks for the quick response.

    I am afraid I am not following you entirely though.

    I added the VBA to a new module, but I am not sure how to add it to the query.

    This is a field in a larger table, and I want to still tie it to other data.

    Thanks,
    David

  6. #6
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: Text to Columns in access ???????

    Just create new fields in your query in the QBE grid (the other example I posted used the actual SQL instead).

    So, in the field area you type this for the left one

    MyLeft:SplitField([YourFieldNameHere],"-",1)

    and the middle

    MyMiddle:SplitField([YourFieldNameHere],"-",2)

    and the right one

    MyRight:SplitField([YourFieldNameHere],"-",2)

    and as long as you named the module something other than SplitField (you don't name the module the same name as a procedure inside of it, to make it less difficult to reference).

    And that is it. Just remember to replace YourFieldNameHere with the actual name of your field which has the 12-2-1 in it.
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


  7. #7
    New Member
    Join Date
    Dec 2013
    Posts
    1

    Default Re: Text to Columns in access ???????

    please can u help me to seperate the below i need only the text and plaese be informed that the data type is text

    110502-مصاريف اشتراكات كهرباء و ماء و هاتف
    12-الموجودات المتداولة
    1201-الزبائن
    i need to seperate the text from column in one criteria

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com