Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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,963

    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
    Former Access MVP (2008-2010, 2011)

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

    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
    Former Access MVP (2008-2010, 2011)

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

    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
    Former Access MVP (2008-2010, 2011)

  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,963

    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
    Former Access MVP (2008-2010, 2011)

  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

  8. #8
    New Member
    Join Date
    Feb 2016
    Posts
    6

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

    Quote Originally Posted by boblarson View Post
    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.
    Hi there!
    I have a similar Problem with a text separated with ";". I tried to use your Formula (Thanks for sharing!!) but the system is complaining:

    "There was an error compiling this function
    The Visual Basic module contains a syntax error.
    Check the code, and then recompile it."

    I didn't change anything yet (was going to change my ";" for "-" to check if it works).
    So I just created the Module (Called it sth different) and paste the "Formulas" on my Query. Does somebody now what could be wrong?
    Thanks a lot for your time!

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

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

    Quote Originally Posted by megan80 View Post
    So I just created the Module (Called it sth different) and paste the "Formulas" on my Query!
    1. I don't understand what you mean by "Called it sth different."
    2. Pasted what in your query?
    Bob Larson
    Former Access MVP (2008-2010, 2011)

  10. #10
    New Member
    Join Date
    Feb 2016
    Posts
    6

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

    Quote Originally Posted by boblarson View Post
    1. I don't understand what you mean by "Called it sth different."
    2. Pasted what in your query?
    Ok sorry I explain it slowly. I changed my MS Office Access to English (I was working in German, don't know if that's the reason for the other error I was getting...)

    1. I created a Module with your Code:

    Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As String
    SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1)
    End Function


    I saved it and called it "M_Trennen"

    2. I created a query (Q_Trennen) and (in Design view) added the field I want to separate [Text] + 3 further columns

    Column1: [Text]
    Column2: MyLeft:SplitField([Text];"-";1)
    Column3: MyMiddle:SplitField([Text];"-";2)
    Column4: MyRight: SplitField([Text];"-";2)

    Note that I had to change "," with ";" because Access was complaining.

    Now I get this error, which seems to appear for every line (63.000!!):

    Run-time error '9'.
    Subscript out of range.

    And this result (I just copied first lines):

    Text | My Left | MyMiddle | MyRight
    A1; A1; #Error #Error
    Q5 Q5 #Error #Error
    Q5- Q5
    X4-Usluga X4 Usluga Usluga
    X7-Kosten 2015 X7 Kosten 2015 Kosten 2015

    Do you know why I get the Run-Time error?
    Can I easily replace the "-" for a ";"?
    I could have up to 5 different codes separated with ";" can I do more Middleparts?
    I am sorry for all the questions but I am trying for days to find a solution to that and I don't get it. I just did it with Excel until now but it's so annoying having to use Excel only for this.
    Many thanks in advance!

Page 1 of 2 12 LastLast

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