[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Data seperation in a field

Posted on 2010-09-17
Medium Priority
Last Modified: 2012-05-10
I have a table with data
in a field that I need
seperated into other fields.
The field with the data is called
The data is seperated by a ".",
like ED.RAM.WARN.L131.P161.
The first segment Needs to go
into a field called "Distribution".
The second segment needs to go
into a field called "District".
The third segment needs to go into
a field called Sub_Area.
The fourth segment needs to go
into a field called LineNo.
The fifth segment needs to go
into a field called "Line_Seg".
There are other fields in this
table, so basically I would be
adding the five new fields.
Any help is greatly appreciated.

Question by:donnie91910
LVL 61

Accepted Solution

HainKurt earned 1000 total points
ID: 33703427
create this in modules

Function splitNGet(s As String, n As Integer, seperator As String)
  Arr = Split(s, seperator)
  splitNGet = (Arr(n - 1))
End Function


splitnget("ED.RAM.WARN.L131.P161.",1,".") as Distribution,
splitnget("ED.RAM.WARN.L131.P161.",2,".") as District,
from MyTable
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 33703438
you will need a UDF function to do that

place this codes in a regular Module (not the Form module)

Function SplitField(ByVal idx As Integer, ByVal sValue As String) As String

    Dim infArr() As String
    SplitField = ""
    If sValue <> "" Then
        infArr = Split(sValue, ".")
        Select Case idx
            Case 1
                SplitField = infArr(0)
            Case 2
                If UBound(infArr) > 0 Then SplitField = infArr(1)
            Case 3
                If UBound(infArr) > 1 Then SplitField = infArr(2)
            Case 4
                If UBound(infArr) > 2 Then SplitField = infArr(3)
            Case 5
                If UBound(infArr) > 2 Then SplitField = infArr(4)

        End Select
    End If
End Function

to use in a query

select [FLOC_LABEL]
,SplitField(1,[FLOC_LABEL]) as Distribution
,SplitField(2,[FLOC_LABEL]) as District
,SplitField(3,[FLOC_LABEL]) as Sub_Area
,SplitField(4,[FLOC_LABEL]) as LineNo
,SplitField(5,[FLOC_LABEL]) as LineSeq
From NameOftable

you can also use the a query to update the fields in your table
by converting the Select query into an Update query


Author Closing Comment

ID: 33704210
worked great.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question