EXTRACT PART OF STRING DELIMITED BY SPACES

[Guest]

I have a field which I need to break up in 3 different ones. This field has
the following format:

XXX YYYY ZZZZ
XXX YYYYYY ZZZ
XXX YYY ZZZZZZ

The number of digits in each block of letters can vary. The only sure thing
is that there is a space between the blocks. How can I extract the different
blocks in 3 different fields?

Thanks for any help

 

[Michel Walsh]

Hi,


InStr( TRIM(fieldName), " ") will spot the first space (TRIM remove any
beginning space in front of the XXX ), Left(TRIM(fieldname),
firstOccurenceOfSpace - 1) retrieve the first part, Mid( TRIM(fieldName),
firstOccurenceOfSpace + 1 ) retrieve what is left. Repeat the recipe on
what is left to get the YYYYY part, then the ZZZZZ part You can also use the
InStrRev( ) to get the last occurrence of something.

If you were in VBA, you could use Split( ) to cut the string into its
segment, the result of split is an array with the segments, but an "array"
cannot be handled directly in a query, where a given field can handle just
one single value, not an array... but, you can use

? split("aaaa bbb cc")(0)
aaaa

which returns the first, 0th, element of the array resulting from split( ).
The syntax, someName()(), may surprise, if it is the first time you are
confronted to it, but it is just a function call, followed by a selection
through an index of an array, array returned by the function.

Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks a lot Michael. This did solve my problem and taught me some new
instructions.

 

[Joshua A. Booker]

Claudio,

This is an example of using the split function in a query:

Function GetStringPart(stInput As String, stDelim As String, intPart As
Integer) As String
Dim Temp
Temp = Split(stInput, stDelim)
On Error Resume Next
GetStringPart = Temp(intPart - 1)
End Function

You can call this from a query like this:

Part1: GetStringPart([FieldName]," ", 1)
Part2: GetStringPart([FieldName]," ", 2)
Part3: GetStringPart([FieldName]," ", 3)

You can also reuse this function to split any string having any number of
parts using any delimiter.

HTH,
Josh

 

[Guest]

Thanks a lot Josh.

Claudio,

This is an example of using the split function in a query:

Function GetStringPart(stInput As String, stDelim As String, intPart As
Integer) As String
Dim Temp
Temp = Split(stInput, stDelim)
On Error Resume Next
GetStringPart = Temp(intPart - 1)
End Function

You can call this from a query like this:

Part1: GetStringPart([FieldName]," ", 1)
Part2: GetStringPart([FieldName]," ", 2)
Part3: GetStringPart([FieldName]," ", 3)

You can also reuse this function to split any string having any number of
parts using any delimiter.

HTH,
Josh

Thanks a lot Michael. This did solve my problem and taught me some new
instructions.