how do I parse a string in acess by "," and put it in different fields

[ambridge]

how do I parse a string in acess by "," and put it in different fields.It's not being possible by saving as text and then importing it because it's too huge.it has to be done by vba code.
ex:

LINK_SUCCESSORS
99 OM Tests,Orders.LU 120802.T11278/003.07 Work Up,Orders.LU 120802.T11287/001.07 Work Up,Orders.LU 120802.T11293/009.07 Work Up,Orders.LU 120802.T11297/003.07 Work Up,Orders.LU 120802.T11295/013.07 Work Up,Orders.LU 120802.T11294/011.07 Work Up
all these have to go to different fields

field1
99 OM Tests

field2
Orders.LU 120802.T11278/003.07 Work Up

field3
Orders.LU 120802.T11287/001.07 Work Up

Pls let me know as soon as possible
Thanks

 

[MichaelRed]

see split in help

wheather or not you opt for code, split can/should be part of your soloution -but the string is far from 'huge', occuping 245 characters in your example.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Passenger]

Hi,

Maybe this code can help you with the parsing problem.

Public Sub Oplos(Mystring As String)
Dim pos(7) As Integer
Dim MyValues(7) As String
Dim i, j, counter, startpos As Byte

counter = 0
For i = 2 To Len(Mystring) Step 1
If Mid$(Mystring, i, 1) = "," Then
counter = counter + 1
pos(counter) = i
End If
Next i
startpos = 1
For j = 1 To counter
MyValues(j) = Mid(Mystring, startpos, pos(j) - startpos)
startpos = pos(j) + 1
'Write here what you want to do with the results
'Just to show you what the values are, I've used Msgbox
MsgBox MyValues(j)
Next j
End Sub

 

[MichaelRed]

Passenger,

please read the advice proffered re the split function. It could be of value to you.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SBendBuckeye]

Michael,

I couldn't find anything on split in either Access97 or Access2000 help.

 

[MichaelRed]

it is available (as an 'intrinsic' function) only in ver 2K and later. I (along with many others) have posted a 'equivalent' routine for prior versions. Mine is 'basSplit', which is easily found through the advanced search in several of the Tek-tips fora.

Failing to find it in help (ver 2K & +) possibly indicates that you are looking in Ms. A. help, not in VB(A) help. Otherwise, your installation is incomlete.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

The Table
WholeName NameF NameL
WholeName FirstName LastName
W.N. NICKOLOFT
JASEF MOORE
BARRY O'BRYAN
A.R. CASTLEBERRY

the query SQL
SELECT tblNames.WholeName, Left([WholeName],InStr([WholeName]," &quot) AS NameF, Mid([WholeName],InStr([WholeName]," &quot) AS NameL
FROM tblNames
WITH OWNERACCESS OPTION;



The Query Results
W.N. NICKOLOFT W.N. NICKOLOFT
JASEF MOORE JASEF MOORE
BARRY O'BRYAN BARRY O'BRYAN

Of course you need to change the (table / field) names to protect...

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

The Table
WholeName NameF NameL
WholeName FirstName LastName
W.N. NICKOLOFT
JASEF MOORE
BARRY O'BRYAN
A.R. CASTLEBERRY

the query SQL
SELECT tblNames.WholeName, Left([WholeName],InStr([WholeName]," &quot) AS NameF, Mid([WholeName],InStr([WholeName]," &quot) AS NameL
FROM tblNames
WITH OWNERACCESS OPTION;



The Query Results
W.N. NICKOLOFT W.N. NICKOLOFT
JASEF MOORE JASEF MOORE
BARRY O'BRYAN BARRY O'BRYAN

Of course you need to change the (table / field) names to protect...

MichaelRed
m.red@att.net

Searching for employment in all the wrong places