Splitting Multivalue field in to multiple fields

[yog2go]

I have a multivalue field in access.
The values in this field are:

er4fa,iuds45,dfa34
fas2,fdpeee3,ffa2

How can i seperate this values and turned them into fields or columns in Access using VBA code?

 

[smedvid]

You could write a little parser function. below is some psuedo code.. It is most likely not the optimal was, but I think it works... hthw,

Function Testit()
Dim lcField1 As String
Dim lcField2 As String
Dim lcField3 As String
Dim lcString As String
Dim lcDelimiter As String
lcString = "field1,field2,field3"
lcDelimiter = ","
lcField1 = ParseData(lcString, lcDelimiter, 1)
lcField2 = ParseData(lcString, lcDelimiter, 2)
lcField3 = ParseData(lcString, lcDelimiter, 3)
End Function

Function ParseData(lcStr As String, lcDelim As String, lnPos As Integer) As String
Dim lcField As String
Dim lnStartPos As Integer
Dim lnEndPos As Integer
Dim lnEndPosLast As Integer
Dim lnLength As Integer
Dim lnDelimPos As Integer

'"field1,field2,field3"
'Get Start/End Positions
lnEndPosLast = 0
lnDelimPos = 1
lnStartPos = 1
For ICnt = 1 To lnPos
lnEndPos = InStr(lnEndPosLast + 1, lcStr, lcDelim)
If lnEndPos = 0 Then
lnStartPos = lnEndPosLast + 1
lnEndPos = Len(lcStr) + 1
Exit For
Else
If ICnt = lnPos Then
lnStartPos = lnEndPosLast + 1
Exit For
End If
End If
lnEndPosLast = lnEndPos
Next ICnt

lnLength = lnEndPos - lnStartPos

lcField = Mid(lcStr, lnStartPos, lnLength)
ParseData = lcField

End Function

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[PHV]

This may be be of interest for you:

http://www.tek-tips.com/viewthread.cfm?qid=904308

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LaCour]

The Split function can parse them into an array, which gets them into multiple fields.

'This dims an unbound array that will accept your values
Dim sStringToBeParsed1() As String
Dim sStringToBeParsed2() As String

sStringToBeParsed1() = Split(sYourField1, ",")
sStringToBeParsed2() = Split(sYourField2, ",")

.................................................

sStringToBeParsed1(0) will contain er4fa
sStringToBeParsed1(1) will contain iuds45
sStringToBeParsed1(2) will contain dfa34

sStringToBeParsed2(0) will contain fas2
sStringToBeParsed2(1) will contain fdpeee3
sStringToBeParsed2(2) will contain ffa2

Hope it helps