Separate a Record based on ":"

i have a record field called FULLNAME that looks like the following:


however, not all records have a Partnumber indicated since they may be the parent.

an example-


I would like a query that will separate them as follows, taking into account that the partnumber field may be blank/null

Category               Division              Manufacturer                   PartNumber
Sales                      Tools                  Acme                                 null
Sales                      Tools                  Acme                                 abc
Sales                      Tools                  Zenith                                null
Sales                      Tools                  Zenith                                123

I prefer ONE query instead of a separate query to extract each divisor..

help is appreciated.

Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
place this codes in a regular module

Function fnParseInfo(vString As Variant, idx As Integer, Optional Delimiter As String = ":") As String

   Dim myArray() As String
   myArray = Split(vString, Delimiter)

   If idx < 0 Or idx > UBound(myArray) + 1 Then

      fnParseInfo = "Null"


      fnParseInfo = myArray(idx - 1)

   End If

End Function

create a query like this

SELECT fnParseInfo([FULLNAME],1) AS Category, fnParseInfo([FULLNAME],2) AS Division, fnParseInfo([FULLNAME],3) AS Manufacturer, fnParseInfo([FULLNAME],4) AS PartNumber
FROM TableName
Jeffrey CoachmanMIS LiasonCommented:
quick and dirty:

copy/ paste this data into Excel

Use the Data-->Text-->To Columns feature to split this data into separate fields.
(Set the delimiter as the colon)

Then copy/paste the resulting Excel data into Access as a new table.
Dale FyeCommented:
I use a function:
Public Function fnParse(TextToParse As String, Position As Integer, Optional Delimiter As String = " ") As Variant

    Dim strArray() As String
    strArray = Split(TextToParse, Delimiter)
    If Position > UBound(strArray) + 1 OR Position < LBOUND(strArray) + 1 Then
        fnParse = NULL
        fnParse = strArray(Position - 1)
    End If
End Function

Open in new window

Then, in the query, I would do:

SELECT fnParse([yourField], 1, ":") as Category,
             fnParse([yourField], 2, ":") as Division,
             fnParse([yourField], 3, ":") as Manufacturer,
             fnParse([yourField], 4, ":") as PartNumber
FROM yourTable
intsupAuthor Commented:
sorry for the delay in accepting a solution.  

Capricorn's came out the quickest and easiest for keeping it within access.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.