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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rey Obrero (Capricorn1)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
intsupAuthor Commented:
sorry for the delay in accepting a solution.  

Capricorn's came out the quickest and easiest for keeping it within access.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.