This guy breaks a string apart on a space ' ', and returns up to 8 columns.
I used an address field as the input, but it could be adapted for anything
of course.


SELECT
Table.AddressColumn AS Address,
' ' as SplitChar,
InStr([Address],SplitChar) as Space1,
IIf(Space1>0, InStr(Space1 + 1, [Address],SplitChar), Null) as Space2,
IIf(Space2>0, InStr(Space2 + 1, [Address],SplitChar), Null) as Space3,
IIf(Space3>0, InStr(Space3 + 1, [Address],SplitChar), Null) as Space4,
IIf(Space4>0, InStr(Space4 + 1, [Address],SplitChar), Null) as Space5,
IIf(Space5>0, InStr(Space5 + 1, [Address],SplitChar), Null) as Space6,
IIf(Space6>0, InStr(Space6 + 1, [Address],SplitChar), Null) as Space7,
IIf(Space7>0, InStr(Space7 + 1, [Address],SplitChar), Null) as Space8,

Left(Address,InStr([Address],SplitChar) -1) AS Address_Part1,
IIf(Space2>0, Mid(Address,Space1+1,Space2-Space1-1), IIf(Space2 = 0,
Mid(Address,Space1+1), Null)) as Address_Part2,
IIf(Space3>0, Mid(Address,Space2+1,Space3-Space2-1), IIf(Space3 = 0
,Mid(Address,Space2+1), Null)) as Address_Part3,
IIf(Space4>0, Mid(Address,Space3+1,Space4-Space3-1), IIf(Space4 = 0,
Mid(Address,Space3+1), Null)) as Address_Part4,
IIf(Space5>0, Mid(Address,Space4+1,Space5-Space4-1), IIf(Space5 = 0,
Mid(Address,Space4+1), Null)) as Address_Part5,
IIf(Space6>0, Mid(Address,Space5+1,Space6-Space5-1), IIf(Space6 = 0,
Mid(Address,Space5+1), Null)) as Address_Part6,
IIf(Space7>0, Mid(Address,Space6+1,Space7-Space6-1), IIf(Space7 = 0,
Mid(Address,Space6+1), Null)) as Address_Part7,
IIf(Space8>0, Mid(Address,Space7+1,Space8-Space7-1), IIf(Space8 = 0,
Mid(Address,Space7+1), Null)) as Address_Part8

FROM Table

Builder wrote:

Another option. I might create a function and call the function
instead...for readability.

SELECT AddressColumn AS Address,
ParseString([AddressColumn]," ",1) As Col1,
...
ParseString([AddressColumn]," ",8) As Col8 From Table1

which would call function ParseString().


Public Function ParseString(varString As Variant, _
strDelim As String,_
intPos As Integer) As Variant

Dim s As String
Dim ar() As String

ParseString = Null

If Not IsNull(varString) Then
ar = Split(varString)
ar = Split(varString, strDelim)

If UBound(ar) >= intPos - 1 Then
ParseString = ar(intPos - 1)
End If
End If
End Function

Nice. Faster than my InStr query too!

I figured a function would be less kludgey.

I take it

Dim s As String
and
ar = Split(varString)

are remnants of development process?

Thanks




Salad wrote:

Builder wrote:

Yup. :-)

Good catch.

The delim parameter wasn't really needed, but the option to pass other
delimiters was added.

I'm not sure what other folks think about passing functions in a query
but it'd be easier to modify the code than your SQL statement if an
update were required.

And, fi we really into shorting code, you can go:

Public Function Gtoken(str As Variant, TokenNum As Integer) As Variant

On Error Resume Next
If IsNull(str) = False Then
Gtoken = Split(str, " ")(TokenNum - 1)
End If

End Function



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

Albert D. Kallal wrote:

Nice. I often forget about Resume Next, 'cause I'm conditioned to trap
every "bug" and tell the user.


This ***even shorter!!!*** version lets you split on whatever character
delimiter you want (you hard-coded the space " ")

Public Function ParseString(str As String, del As String, pos As Byte) As
Variant
On Error Resume Next
ParseString = Split(str, del)(pos - 1)
End Function

Builder wrote:
query, and one passed a null or a integer parameter beyond the array's
UBound value you don't return a null

s = "Joe Blow"
x= ParseString(s,5)
? IsNull(x)
False
? isEmpty(x)
True
s = Null
x= ParseString(s,1)
? IsNull(x)
False
? isEmpty(x)
True

It doesn't affect checking for IsNull from results of the query tho.

"Builder" <nospam (AT) buildit (DOT) com> wrote




Cute.......

Salad wrote:
I think it's just about over...

I started off by trying a simple function, but it didn't work first time so
I went with my slow InStr-query, which works fine but is kludgey and harder
to use and maintain. Your 8-line function is much better, and my 2-line
minimalist version of Kallal's is just what the Dr. ordered.

What's particularly cool about his version is doing the Split and the
assignment in one line.

ParseString = Split(inString, delimiterCharacter)(arrayPosition - 1)

Albert, how did you figure this out, rather than do the Split then check the
array as Salad did?




My version won't accept an integer. If I pass a Null the query results in
#Error in each column.

My final version in this thread:

Public Function ParseString(str As Variant, del As String, pos As Byte) As
Variant
On Error Resume Next
ParseString = IIf(Not IsNull(str), Split(str, del)(pos - 1), Null)
End Function