thank you for a great simple solution.
John Spencer wrote:
One method would be to use a custom vba function - see code below.
08-Jan-10
One method would be to use a custom vba function - see code below. Copy an
paste this into a VBA module
'================================================
Public Function fGetToken(strIn,
Optional strDelimiter As String = " ",
Optional LPos As Long = 1
'Return the Nth item from a delimited list of items
Dim strArr As Varian
If Len(strIn & "") = 0 The
fGetToken = strI
Els
strArr = Split(strIn, strDelimiter
If LPos - 1 <= UBound(strArr) The
fGetToken = strArr(LPos - 1
Els
fGetToken = Nul
End I
End I
End Functio
'================================================
In your query you could use expressions like
Field: Field1: fGetToken([TheExistingField],"/",1
Field: Field2: fGetToken([TheExistingField],"/",2
In SQL view that would look somethig lik
SELECT fGetToken([TheTable].[TheExistingField],"/",1) as Field
, fGetToken([TheTable].[TheExistingField],"/",2) as Field
, ..
, fGetToken([TheTable].[TheExistingField],"/",7) as Field
FROM [TheTable
John Spence
Access MVP 2002-2005, 2007-201
The Hilltop Institut
University of Maryland Baltimore Count
Jani wrote:
Previous Posts In This Thread:
Parsing 1 field to 7 - divided by "/"
I have a table where one field needs to be parsed into 7 columns. Each fiel
is separated by a "/" - examples shown below. I am most familiar with querie
and would like some help in how to write a criteria to do this. Thanks i
advance! Jan
-/-/698500420/INTRCO/-/-/100
TEM/05000/300100/160020/34097/-/217
TEM/11600/-/-/02480/-/-
I believe you will need code to split the string.
I believe you will need code to split the string. Try create a new module an
paste this function into it
Function SplitString(strIn As String, strDelim As String,
intPart As Integer, Optional booTrim As Boolean = True) As Strin
Dim Ar
'Arrays are zero base
intPart = intPart -
Ary = Split(strIn, strDelim
If intPart >= 0 And intPart <= UBound(Ary) The
If booTrim The
SplitString = Trim(Ary(intPart)
Els
SplitString = Ary(intPart
End I
Els
SplitString = "
End I
End Functio
Save the module as "modStringFunctions". You can then use the function like
Column7: SplitString([one field],"/",7
-
Duane Hooko
Microsoft Access MV
:
Here is one way. it is seriously ugly, but it works.SELECT s.
Here is one way. it is seriously ugly, but it works
SELECT s.src, Left([src],InStr([src],"/")-1) AS Col1
Mid([src],InStr([src],"/")+1,InStr(InStr([src],"/")+1,[src],"/")-InStr([src],"/")-1
AS Col2
Mid([src],InStr(InStr([src],"/")+1,[src],"/")+1,InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")-InStr(InStr([src],"/")+1,[src],"/")-1
AS Col3
Mid([src],InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,InStr(InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")-InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")-1
AS Col4
Mid([src],InStr(InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,InStr(InStr(InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")-InStr(InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")-1
AS Col5
Mid([src],InStr(InStr(InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,InStr(InStr(InStr(InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")-InStr(InStr(InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")-1
AS Col6
Mid([src],InStr(InStr(InStr(InStr(InStr(InStr([src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1,[src],"/")+1
AS Col
FROM s
s is the table, src is the field to be split up
Pete
One method would be to use a custom vba function - see code below.
One method would be to use a custom vba function - see code below. Copy and
paste this into a VBA module.
'=================================================
Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
'Return the Nth item from a delimited list of items.
Dim strArr As Variant
If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If
End Function
'=================================================
In your query you could use expressions like:
Field: Field1: fGetToken([TheExistingField],"/",1)
Field: Field2: fGetToken([TheExistingField],"/",2)
In SQL view that would look somethig like
SELECT fGetToken([TheTable].[TheExistingField],"/",1) as Field1
, fGetToken([TheTable].[TheExistingField],"/",2) as Field2
, ...
, fGetToken([TheTable].[TheExistingField],"/",7) as Field7
FROM [TheTable]
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Jani wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Featured Product / Service Review: TekPub
http://www.eggheadcafe.com/tutorial...2e-39384482c80e/featured-product--servic.aspx