Splitting a comma separated field into 4

arpgis

New member
Local time
Today, 12:30
Joined
Jan 30, 2007
Messages
2
Hi
I'm having a senior moment...

I have a reference field - examples below and need to split into the component parts

example Ref_IDs:
C21/0051,Sheet 18, Sheet 19, E 3, 12/01/07
C21/0052.04, Sheet21 ,E 2
C21/0054, Sheet 23, B 2, 05/01/07

First - ref no, Second - Sheet (note some have more than one), Third - Grid and lastly some have dates, but not all
 
Copy/paste this into your Immediate Window in the code window (Ctrl-G is the shortcut for this window):

arySplitter = Split("C21/0051,Sheet 18, Sheet 19, E 3, 12/01/07", ",")
MsgBox arySplitter(0) & vbCrLf & arySplitter(1) & vbCrLf & arySplitter(2) & vbCrLf & arySplitter(3) & vbCrLf & arySplitter(4)


This should give you a clear idea of what the Split Function is doing. Look at MS Access Help for more details and search this forum for more examples.
 
Thanks Moniker,
I thought the Split Function was only in access 2007?
I only have access 2003 (should have made that clear)
Is there a way of splitting the field using queries?
 
Split() has been available since A2K
Just create a custom function based on Monikers code and call it from a query.

HTH

Peter
 
Here you go:

Code:
Function Splitter(strToSplit As String, strDelimiter As String, ReturnIndex As Byte) As Variant

    Dim arySplitter As Variant

    If Not (IsNull(strToSplit)) Then
        arySplitter = Split(strToSplit, strDelimiter)
        Splitter = arySplitter(ReturnIndex)
    Else
        Splitter = ""
    End If

End Function

strToSplit is the string you want to split.
strDelimiter is the separator in your string between values (a comma in your example).
ReturnIndex is the array member to return.

For example, if I want to split "1,2,3,4,5" and return the "3", I would do:

=Splitter("1,2,3,4,5",",",2)

Remember that arrays are zero-based, so:

Splitter(0) = 1
Splitter(1) = 2
Splitter(2) = 3
Splitter(3) = 4
Splitter(4) = 5

To use this in your query, just call it, something like this:

=Splitter([Name_Of_Field_To_Split], ",", IndexToReturn)
 
Last edited:
This what I have been looking for!!! I do have a quick question...I am getting an error if there isnt an element of the string left to split. Is there a way to do something like if null="".

I apologize if this is a stupid question, but I am extremely new. It will have to be spelled out to me
 
Phil,

You can change Moniker's function adding some code after this line:

Splitter = arySplitter(ReturnIndex)

Code:
If UBound(arySplitter) < ReturnIndex Then
   Splitter = ""
   Exit Function
End If

hth,
Wayne
 

Users who are viewing this thread

Back
Top Bottom