SPLITTING FIELDS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to split a single field that has multiple part numbers with a comma deliminator within it to multiple fields. Currently I can get the the first part by using the left function, last part by useing right function. I need to extract the part numbers in the middle.
 
=?Utf-8?B?VEVE?= said:
I am trying to split a single field that has multiple part numbers with a
comma deliminator within it to multiple fields. Currently I can get the the
first part by using the left function, last part by useing right function. I
need to extract the part numbers in the middle.
Click to expand...

There are some functions on the Access Web that deal with counting the number
of comma separated words, and then returning the 1st, 2nd, etc word from that
string. Have a look at http://www.mvps.org/access/strings/str0003.htm for the
code.

I would use these functions in a code module to loop through a recordset based
on the table, and write each part number to a new record, rather than trying to
split this in a query.
 
TED said:
I am trying to split a single field that has multiple part numbers with a comma
Click to expand...
deliminator within it to multiple fields. Currently I can get the the first part by
using the left function, last part by useing right function. I need to extract the
part numbers in the middle.

Hi Ted,

In addition to Jon's sage advise,
here be a function adapted from
one Dirk kindly provided on this ng
(if you are using Access 200x).

'*** start of code ***
Function fNthElement( _
KeyString As Variant, _
Optional Delimiter As String = ";", _
Optional ElementNo As Integer = 1) _
As Variant
'Splits KeyString into "ElementNo" of strings
'separating out by Delimiter and
'Returns "ElementNo"th string if it exists.
'----------
'Returns Null if KeyString
' - is null, zero-length string, or all spaces
' - does not contain Delimiter
' - ends with Delimiter
' - cannot be split into "ElementNo" of strings

On Error GoTo Err_fNthElement
Dim arrSegments As Variant

If Len(Trim(KeyString & "")) > 0 _
And InStr(KeyString, Delimiter) > 0 _
And Right(KeyString, 1) <> Delimiter Then
arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElement = arrSegments(ElementNo - 1)
Else
fNthElement = Null
End If
Else
fNthElement = Null
End If

Exit_fNthElement:
Exit Function

Err_fNthElement:
MsgBox Err.Description
Resume Exit_fNthElement
End Function
'*** end of code ***

Save the function above in a module
(if you are creating a new module,
please name your module as anything
other than "fNthElement")

In your query, to find 2nd segment in your field (say "f1")

SELECT ...,
fNthElement([f1], ",", 2) As 2ndSeg
FROM ...

Please note cases where it will return Null.

Good luck,

Gary Walter
 
Back
Top