Page 1 of 5 12345 LastLast
Results 1 to 15 of 62
  1. #1
    evilleve is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Tehran
    Posts
    31

    Question help me in , separate a text to several columns ?

    i have a field that contain string , like this :


    (15-SW-037[SMAW]#0~300,RS,RES)
    (15-SW-042[SMAW]#0~0,GM,RET#1600~1700,SL,REP)
    etc ....
    i want to split them to several fields : field1 :15-sw-037 Field 2 : [smaw] Field 3 : 0~0 ,etc ....

    is there any way to do this ?

    thanks in advance

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm sure there is but more information would need to be provided for a more complete answer. It would almost certainly involve VBA code and string searches.

  3. #3
    evilleve is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Tehran
    Posts
    31
    Quote Originally Posted by RuralGuy View Post
    I'm sure there is but more information would need to be provided for a more complete answer. It would almost certainly involve VBA code and string searches.
    Thank you for answering me

    i try to use split function , but i'm newbie in vba
    i want to know :
    Is it possible to use the Split Function in a query?
    and how i can separate a string like below , using split function ?

    my field name is Welders
    it contain strings like this :
    (15-SW-001[SMAW]#0,534,1,12,80%,LOF,REP)(15-SW-001[SMAW]#0,534,1,12,80%,LOF,REP)

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The Split() function is very useful if all of your values are separated by a common separator like ";" or a space. In order to use the Split() function in a query you would need to create your own user defined function (UDF).

  5. #5
    evilleve is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Tehran
    Posts
    31
    Quote Originally Posted by RuralGuy View Post
    The Split() function is very useful if all of your values are separated by a common separator like ";" or a space. If order to use the Split() function in a query you would need to create your own user defined function (UDF).
    in first step seperator is "()" , then "#" , then ","
    i can seperate them step by step
    but my problem is creating this split() function

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You are welcome to use the split function and I'm sure that it can be applied here but it would require a function (UDF) to use it.

  7. #7
    evilleve is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Tehran
    Posts
    31
    Quote Originally Posted by RuralGuy View Post
    You are welcome to use the split function and I'm sure that it can be applied here but it would require a function (UDF) to use it.
    bro what did you mean from UDF ?
    and how can i create it ?

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by evilleve View Post
    bro what did you mean from UDF ?
    and how can i create it ?
    Explained in posrt #4.

  9. #9
    evilleve is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Tehran
    Posts
    31
    for example i creat a function like below to separate a string field like this
    aa-asda-sdhdhg
    a
    shjkk-whhsa
    sd-dfa


    Public Function mysplit1(oldfield As String)
    myarray = Split(oldfield, "-")
    mysplit1 = myarray(0)
    End Function

    Public Function mysplit2(oldfield As String)
    myarray = Split(oldfield, "-")
    mysplit2 = myarray(1)
    End Function

    Public Function mysplit3(oldfield As String)
    myarray = Split(oldfield, "-")
    mysplit3 = myarray(2)
    End Function

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would suggest something like:
    Code:
    Public Function MySplit(InString As String, MyDelim As String, InOffset As Integer) As String
    Dim MyArray() As String
       MyArray = Split(InString, MyDelim)
       MySplit= MyArray(InOffset)
    End Function

  11. #11
    evilleve is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Tehran
    Posts
    31
    Quote Originally Posted by RuralGuy View Post
    I would suggest something like:
    Code:
    Public Function MySplit(InString As String, MyDelim As String, InOffset As Integer) As String
    Dim MyArray() As String
       MyArray = Split(InString, MyDelim)
       MySplit= MyArray(InOffset)
    End Function
    bro , can you explain for me how it works ?

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Only if you stop calling me "bro". Have you figured out how to invoke it yet? Have you put it in a Standard Module yet?

  13. #13
    evilleve is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2011
    Location
    Tehran
    Posts
    31
    Quote Originally Posted by RuralGuy View Post
    Only if you stop calling me "bro". Have you figured out how to invoke it yet? Have you put it in a Standard Module yet?
    ok

    for field [welders] like this :
    (15-SW-040[SMAW]#0~0,LF,REP)(15-SW-041[SMAW]#0~10,LF)
    (15-SW-094[SMAW]#150~151,LF,REP)
    .
    .
    .

    i put this in a module :

    Public Function MySplit1(welders As String, MyDelim As String, InOffset As Integer) As String
    Dim MyArray() As String
    MyDelim = "("
    MyArray = mySplit1(welders, MyDelim)
    InOffset = 1
    MySplit1 = MyArray(InOffset)
    End Function

    Public Function MySplit2(welders As String, MyDelim As String, InOffset As Integer) As String
    Dim MyArray() As String
    MyDelim = "("
    MyArray = mySplit2(welders, MyDelim)
    InOffset = 2
    MySplit2 = MyArray(InOffset)
    End Function

    i made 2 function , because i need 2 field with these names : welder1 , welder2
    in query i put these : welder1: mysplit1([WELDERS];"(";0)
    welder2: mysplit2([WELDERS];"(";1)

    in windows xp i got an error : argument not optional

    in windows 7 it work good until the function found "(" , when the function cant find "(" an error happens
    Last edited by evilleve; 03-06-2011 at 01:56 AM.

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are you showing two or three records? You do realize you have no consistant delimiter to use, right?

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    To be honest, this is not a good situation to use the Split() function because you do not have delimited data. As I said in my first post, you will probably ned to use string functions to get the values you need. Mid(), Left(), Right(), InStr().

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Separate combo box
    By sanos_a in forum Access
    Replies: 1
    Last Post: 10-07-2010, 05:30 AM
  2. How To Chane Color Of Text In Columns
    By aamer in forum Access
    Replies: 5
    Last Post: 09-12-2010, 09:30 AM
  3. Run 2 Processes in Separate Threads
    By matt_tapia in forum Programming
    Replies: 1
    Last Post: 08-06-2009, 12:33 PM
  4. Separate queries?
    By sid in forum Queries
    Replies: 0
    Last Post: 08-01-2009, 10:31 AM
  5. Separate one field into many
    By ellen in forum Programming
    Replies: 5
    Last Post: 12-22-2008, 06:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums