INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

Thanks so much for having a place for us propeller heads to hang out and chat.

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Tables and Relationships Forum

Truncate a text field into 3 text Fields

Read More Threads Like This One

scubadunc (IS/IT--Management)

26 Nov 10 2:34

Hi there

I have a database that has an "Entity Name" field which in some cases is ridiculously long.  I need to write a query or VBA function that will allow me to truncate only those records that have more than 35 chars into 3 fields, splitting at either an "&" or " " char.

An example is below:

Entity Name
M.R BEARD & D.C COCKING & R.L FEWSTER &    S.L GILL & J.L NIGHT & M.R LOS & V.A POLLARD & J.E RULE & J.K RULE & I YUKICH

to be split into 3 new fields
Entity Name 1
M.R BEARD & D.C COCKING & R.L FEWSTER &
Entity Name 2
S.L GILL & J.L NIGHT & M.R LOS &
Entity Name 3
V.A POLLARD & J.E RULE & J.K RULE & I YUKICH

I really don't know where to start or if this is even possible.  Any help or script would be wonderful.

Regards

Duncan

alvechurchdata (Programmer)

26 Nov 10 3:24

Look at using the Split function. It'll break the string into an array of words and you can choose which character to use as the break.

Geoff Franklin
www.alvechurchdata.co.uk

MajP (TechnicalUser)

26 Nov 10 18:17

It is a relational database so splitting this into three fields makes as little sense as the original design. Each value should likely be in its own record in a related table. As Geoff said the split function would be a starting point.

scubadunc (IS/IT--Management)

28 Nov 10 20:11

Thanks for your replies. I'm very rusty with access functions these days, any ideas on what the code should look like.

The reason behind the requirement of splitting the fields is for a mail merge application in a DOS based environment (I know, I know get with the times right). But as you are aware, there is little control in this sort of app. So when merging the name and address fields there is no wrap text option.

Any ideas would be appreciated.

Regards

Duncan

scubadunc (IS/IT--Management)

5 Jan 11 2:48

Hi there

Just to reiterate, can someone show me some SQL that would enable me to split the below text into approx 40 characters per block, separating at the next available "&" char?

An example is below:

Original Data
Entity Name
M.R BEARD & D.C COCKING & R.L FEWSTER & S.L GILL & J.L NIGHT & M.R LOS & V.A POLLARD & J.E RULE & J.K RULE & I YUKICH

to be split into 3 new fields
Entity Name 1
M.R BEARD & D.C COCKING & R.L FEWSTER &
Entity Name 2
S.L GILL & J.L NIGHT & M.R LOS &
Entity Name 3
V.A POLLARD & J.E RULE & J.K RULE & I YUKICH

Andrzejek (Programmer)

5 Jan 11 9:13

How about this:

CODE

Dim strEntName As String
Dim strEN(5) As String
Dim i As Integer
Dim l As Integer

strEntName = "M.R BEARD & D.C COCKING & R.L FEWSTER & S.L GILL & J.L NIGHT & M.R LOS & V.A POLLARD & J.E RULE & J.K RULE & I YUKICH"

For l = LBound(strEN) To UBound(strEN)
    If Len(strEntName) > 40 Then
        For i = 40 To 1 Step -1
            If Mid(strEntName, i, 1) = "&" Then
                strEN(l) = Left(strEntName, i)
                strEntName = Mid(strEntName, i + 2)
                Exit For
            End If
        Next i
    Else
        If Len(strEntName) > 0 Then
            strEN(l) = strEntName
            strEntName = ""
        End If
    End If
Next l

For l = LBound(strEN) To UBound(strEN)
    Debug.Print strEN(l)
Next l

You get:

M.R BEARD & D.C COCKING & R.L FEWSTER &
S.L GILL & J.L NIGHT & M.R LOS &
V.A POLLARD & J.E RULE & J.K RULE &
I YUKICH

Have fun.

---- Andy

scubadunc (IS/IT--Management)

5 Jan 11 20:42

Thanks for the code Andy

I can pass the variable "Entity Name" field into the function but how would I go about placing the outputted data into 3 or 4 or more separate fields within Access? I need to export the truncated data to ascii text for use within a DOS based environment.

Thanks in Advance

Duncan

Andrzejek (Programmer)

6 Jan 11 8:17

Somebody else would have to help you.
I am not an Access guy, I deal with VB 6 and VB.NET with ORACLE
Access it kind of backwards to me as far as VBA...

Have fun.

---- Andy

dhookom (Programmer)

6 Jan 11 12:20

I have a couple functions in my code library that might help. The first sticks a character in your long string at user-defined lengths.

CODE

Function SectionIt(strTxt As String, _
        intBreakAt As Integer, _
        strFindBreak As String, _
        strBreakChar As String) As String
    Dim strReturn As String
    Dim strLine As String
    Dim intFoundAt As Integer
    Dim strWorkingWith As String
    strWorkingWith = strTxt
    Do Until Len(strWorkingWith) = 0
        strLine = Left(strWorkingWith, intBreakAt + 1)
        intFoundAt = InStrRev(strLine, strFindBreak)
        If intFoundAt > 0 Then
            strReturn = strReturn & Left(strLine, intFoundAt - 1) & strBreakChar
            strWorkingWith = Mid(strWorkingWith, intFoundAt + 1)
         Else
            strReturn = strReturn & strLine
            strWorkingWith = ""
        End If

    Loop
    SectionIt = strReturn
End Function

Results of this function are:

CODE

?SectionIT("this is a long text string for testing", 12," ", "~")
this is a~long text~string for~testing

The second function can take the results of the SectionIt() function and return the number of the section you want.

CODE

Public Function ParseText(pstrText As String, intElement As Integer, _
        pstrDelimiter As String) As String
    Dim arText() As String
   On Error GoTo ParseText_Error

    arText() = Split(pstrText, pstrDelimiter)
    ParseText = arText(intElement - 1)

ExitParseText:
   On Error GoTo 0
   Exit Function

ParseText_Error:
    Select Case Err
        Case 9 'subscript out of range
            'don't do anything
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ParseText of Module basParseText"
    End Select
    Resume ExitParseText
End Function

Use these together like:

CODE

? ParseText(SectionIt("this is a long text string for testing", 12," ", "~"),3,"~")
string for

Substituting your field name and pulling the 2nd section with max of 35 characters would be:

CODE

ParseText(SectionIt([Entity Name],35," ", "~"),2,"~")

Sorry, I don't have time to re-write these to look for either a space or &.

Duane
Hook'D on Access
MS Access MVP

scubadunc (IS/IT--Management)

6 Jan 11 20:04

Thanks Andy and thanks Duane

Duane, your code is just wat I'm looking for. Thank you so much, will test it out and let you know how I go.

scubadunc (IS/IT--Management)

6 Jan 11 20:46

Hi Duane

The code works really well, although I get sporadic results when using the "&" character.

Thanks again for all your help on this.

Regards

Duncan

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Tables and Relationships

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Truncate a text field into 3 text Fields

CODE

CODE

CODE

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!