Solved

Data seperation in a field

Posted on 2010-09-17
6
307 Views
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
FLOC_LABEL.
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.
Thanks


0
Comment
Question by:donnie91910
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 250 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

use

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

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 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




0
 

Author Closing Comment

by:donnie91910
ID: 33704210
worked great.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Microsoft Access report help 4 35
Access Form - Button Format Issue 13 42
Getting the 7Z zip dll to work with MS Access 8 53
Access Report via Windows Desktop Shortcut 12 39
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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