asked on

Separate a Record based on ":"

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

FULLNAME
Category:Division:Manufacturer:PartNumber

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

an example-

FULLNAME
Sales:Tools:Acme
Sales:Tools:Acme:abc
Sales:Tools:Zenith
Sales:Tools:Zenith:123

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.

thanks!

Jeffrey Coachman

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 Fye

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
    Else
        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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jose C

ASKER

sorry for the delay in accepting a solution.

Capricorn's came out the quickest and easiest for keeping it within access.

Separate a Record based on &quot;:&quot;

Separate a Record based on ":"