Parse one field into multiple columns

[mattc66 via AccessMonster.com]

Hi All,

Need some help. I have read the Mid, Left and Right options. But I have some
data that may very.

The Data will come as follows (1 FLD):
CC,4.5x5x7,C2
CC,4x5x7,C2
PB,5.5x5.5x8,C2

I need to split up the data above into 5 columns.
FLD1 FLD2 FLD3 FLD4 FLD5
CC 4.5 5 7 C2
CC 4 5 7 C2
PB 5.5 5.5 8 C2

Please help.

 

[Duane Hookom]

Your data looks very consistent. If it is always separated like ,xx, then
you can use this function:
Public Function ParseData(pstrText As String, _
pintColumn As Integer) As String
'pstrText is the value in
'pintColumn is the column (1-5)
'parse values like
'CC,4.5x5x7,C2
'CC,4x5x7,C2
'PB,5.5x5.5x8,C2
'to
' 1 2 3 4 5
'== === === = ==
'CC 4.5 5 7 C2
'CC 4 5 7 C2
'PB 5.5 5.5 8 C2
Dim arCSV
Dim arX
'create 3 comma separated values
arCSV = Split(pstrText, ",")
'split the "x" separated values
arX = Split(arCSV(1), "x")
Select Case pintColumn
Case 1
ParseData = arCSV(0)
Case 2
ParseData = arX(0)
Case 3
ParseData = arX(1)
Case 4
ParseData = arX(2)
Case 5
ParseData = arCSV(2)
End Select
End Function

 

[mattc66 via AccessMonster.com]

That looks good - Call me new, but how do I execute. Do I place this in a
query?

Your data looks very consistent. If it is always separated like ,xx, then
you can use this function:
Public Function ParseData(pstrText As String, _
pintColumn As Integer) As String
'pstrText is the value in
'pintColumn is the column (1-5)
'parse values like
'CC,4.5x5x7,C2
'CC,4x5x7,C2
'PB,5.5x5.5x8,C2
'to
' 1 2 3 4 5
'== === === = ==
'CC 4.5 5 7 C2
'CC 4 5 7 C2
'PB 5.5 5.5 8 C2
Dim arCSV
Dim arX
'create 3 comma separated values
arCSV = Split(pstrText, ",")
'split the "x" separated values
arX = Split(arCSV(1), "x")
Select Case pintColumn
Case 1
ParseData = arCSV(0)
Case 2
ParseData = arX(0)
Case 3
ParseData = arX(1)
Case 4
ParseData = arX(2)
Case 5
ParseData = arCSV(2)
End Select
End Function

Hi All,

[quoted text clipped - 14 lines]

Please help.

 

[mattc66 via AccessMonster.com]

I want to be clear, I need to place each seperated value into its own field.
table
fld1 = cc,4.5x5x7,c2 (run the function, and it placed the data into the
proper flds).
fld2 = cc
fld3 = 4.5
fld4 = 5
fld5 = 7
fld6 = c2

Its hard at times to describe without pictures.

Thanks for all your help.

Your data looks very consistent. If it is always separated like ,xx, then
you can use this function:
Public Function ParseData(pstrText As String, _
pintColumn As Integer) As String
'pstrText is the value in
'pintColumn is the column (1-5)
'parse values like
'CC,4.5x5x7,C2
'CC,4x5x7,C2
'PB,5.5x5.5x8,C2
'to
' 1 2 3 4 5
'== === === = ==
'CC 4.5 5 7 C2
'CC 4 5 7 C2
'PB 5.5 5.5 8 C2
Dim arCSV
Dim arX
'create 3 comma separated values
arCSV = Split(pstrText, ",")
'split the "x" separated values
arX = Split(arCSV(1), "x")
Select Case pintColumn
Case 1
ParseData = arCSV(0)
Case 2
ParseData = arX(0)
Case 3
ParseData = arX(1)
Case 4
ParseData = arX(2)
Case 5
ParseData = arCSV(2)
End Select
End Function

Hi All,

[quoted text clipped - 14 lines]

Please help.

 

[Duane Hookom]

You open a new blank Module and paste the function into the module. Save the
module as "modCalcs".
You can then use the function ParseData() almost anywhere you would use any
other function. For instance you can create a column in a query like:
Fld2: ParseData([fld1], 1)
or
Fld6: ParseData([fld1], 5)

--
Duane Hookom
MS Access MVP
--

I want to be clear, I need to place each seperated value into its own
field.
table
fld1 = cc,4.5x5x7,c2 (run the function, and it placed the data into the
proper flds).
fld2 = cc
fld3 = 4.5
fld4 = 5
fld5 = 7
fld6 = c2

Its hard at times to describe without pictures.

Thanks for all your help.

Your data looks very consistent. If it is always separated like ,xx, then
you can use this function:
Public Function ParseData(pstrText As String, _
pintColumn As Integer) As String
'pstrText is the value in
'pintColumn is the column (1-5)
'parse values like
'CC,4.5x5x7,C2
'CC,4x5x7,C2
'PB,5.5x5.5x8,C2
'to
' 1 2 3 4 5
'== === === = ==
'CC 4.5 5 7 C2
'CC 4 5 7 C2
'PB 5.5 5.5 8 C2
Dim arCSV
Dim arX
'create 3 comma separated values
arCSV = Split(pstrText, ",")
'split the "x" separated values
arX = Split(arCSV(1), "x")
Select Case pintColumn
Case 1
ParseData = arCSV(0)
Case 2
ParseData = arX(0)
Case 3
ParseData = arX(1)
Case 4
ParseData = arX(2)
Case 5
ParseData = arCSV(2)
End Select
End Function

Hi All,

[quoted text clipped - 14 lines]

Please help.

 

[mattc66 via AccessMonster.com]

That worked great..

I have another task that is similure. I will create a new thread.

You open a new blank Module and paste the function into the module. Save the
module as "modCalcs".
You can then use the function ParseData() almost anywhere you would use any
other function. For instance you can create a column in a query like:
Fld2: ParseData([fld1], 1)
or
Fld6: ParseData([fld1], 5)

I want to be clear, I need to place each seperated value into its own
field.

[quoted text clipped - 52 lines]