Truncate a text field into 3 text Fields

[scubadunc]

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]

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

http://www.alvechurchdata.co.uk

 

[MajP]

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]

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]

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]

How about this:

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:
[tt]
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[/tt]


Have fun.

---- Andy

 

[scubadunc]

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]

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]

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.

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:

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

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

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:

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

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

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]

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]

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