please help me in,vba code for split a column to several columns

[evilleve]

hi 2 all :hi:
i'm newbie in vba
i searched before ,for spliting a columns to several columns and finally i find how to use split function
but its not enough for me
i have a table ike this

fields => REPAIRWELDERS
rows

record1 (15-SW-037[SMAW]#0~10,RS,RES)(15-SW-039[SMAW]#10~20,RS,RES)
record2 (15-SW-042[SMAW]#0~2,GM,RET#1600~1700,SL,REP)
3
..
...
etc

i must split it to several columns ,

like this :

fields =>... Welder1...... WeldingType ..... Defect1...... Defectlength...... Defect2 ..
record1 15-SW-037 ........SMAW ........... RS,RES.............. 10 ....
record2 15-SW-042 ....... SMAW ............GM,RET.............. 2 .................SL,REP

its just a sample
1- each () is a welder , these () maybe become up to 10
so i must make 10 wlders fields , welder1 , welder2 , 3 , ... 10
2- in each () , welding type can be separated by []
3- each defect specified by # , defect mabe become up to 5
so we have :defect1 , defect2 , ...5
4-defect1 : RS,RES
5-defectlength : in record 2 , for defect1 : 2-0= 2
in record 2 , for defect2 : 1700-1600 = 100

i can do it by using access function (mid , instr)
but when welders become 2 , it says the query is too complex!

i will share the file if its necessary

thanks in advance :bow:

[OBP]

evilleve, welcome to the forum.
I think you should split the data in to 2 seperate tables, one for the Welder & one for the welder's defects.
I would suggest that you post an Access 2000-2003 database with a table of the data that you have shown.
The VBA code will be using the same mid & instr that you used in the query, so supplying the query would also help, but obviously the VBA will be quite complex.

[evilleve]

thanks oBp :)
i'm using access 2007
here is the link :
http : / /
rapidshare.com/files/452167867/Database3.accdb
see the final result query

sorry , my posts are just2 , i cant post full link , so post it on this way

[evilleve]

i used this as a function :

Public Function MySplit(REPAIRWELDERS As String, MyDelim As String, InOffset As Integer) As String
Dim MyArray() As String
MyDelim = "("
InOffset = 1
MyArray = Split(REPAIRWELDERS, MyDelim)
MySplit = MyArray(InOffset)
End Function

it works until the records has a "(" , when the record is null or dont have"(" it says :

Run-time error '9'
subscript out of range

whats the problem ?

[OBP]

Can you post it as an Access 2003 format database please?
I do not have Access 2007.

[evilleve]

thank you for replying me
here is the link :
http ://
rapidshare.com/files/452283851/Database.mdb

[evilleve]

as i said before i'm novice in vba , can u put in the function some error checking built in ?

here is the full link :

http://rapidshare.com/#!download|438dt|452283851|Database.mdb|2256 (http://rapidshare.com/#%21download%7C438dt%7C452283851%7CDatabase.mdb%7C2256)

[OBP]

OK, I have the database.
There does not appear to be any records that have 2 sets of ()?
The othe question is where does the data go to when it is split?

[evilleve]

most of the RepairWelder field records will have 2 and more () , up to 20,soon.
about the second question : in new query , that contain splited RepairWelders and other fields , some of them have relation with splited RepairWelders , some of them not
i need splited RepairWelders for calculating percentage of defect of each welder

[OBP]

OK, I have the code working through the records and splitting the data up.
But there is a problem, these 2 records are not in the same format as all of the rest, they are 611, 612 & 615. They have the format of
(15-SW-001[SMAW]#0,534,1,12,80%,LOF,REP)
Notice they do not have the ~ and also have % values.

Can you also clarify for me the format for the Length of defect, is it
#0~10, = 10
ie the second value or
#150~300 = 150
the second value - the first value
Or the whole thing
#150~300 = 150~300

[evilleve]

yes , Length of defect is
#150~300 = 150
the second value - the first value

about that problem , its a question for me too
i will ask from whom share for me the table

[OBP]

Ok, I still need to work on the repeat defects within each Welder.

[OBP]

OK here it is.
Click the Convert Welder data button and then opent the output query.
It will list the Sheet1 records with the Defects table added to show you the defects for each record.

[evilleve]

nice work .
i have some questions :
in output query , do you split welders "()" in rows ?
if it is so , why each welder repeated for 90 times ?
'when welders be splited in rows i can't use them
because some fields have relations with repair welders'

[OBP]

Sorry I don't understand your question, when you have multiple welders and multiple defects of any quantity in the Repair Welders the only way to work with them is to have a record for each repair for each welder.
The output bring them back together with original weld list in sheet1.

In what way can't you work with them?

PS sorry about leaving the "update" msgbox in th code.