Wrapping memo field for text file

[Helen267]

I have a report which creates a text file for import into a financial system (not in Access) using the OutputTo command. The import procedure requires that each line be no longer than 70 characters. The field being imported is 1000 characters, so I have created a query which uses the Mid function to load 70 characters (consecutively) into separate fields.

This works fine, however the import function in the financial package does not put the fields back together as one long string, and some words are cut in half.

Does anyone have any code which can split my memo field into separate fields (to a maximum of 70 characters) but cutting at the nearest space, rather than in the middle of words?

Any help would be HUGELY appreciated!

 

[redwoodly]

here's some handy dandy parsing functions that combined and checking for the Len(s) should do it:


-------------------------------------------------
Function CountWords(S) As Integer
'
' Counts words in a string separated by 1 or more spaces
'
Dim WC As Integer, i As Integer, OnASpace As Integer
If VarType(S) <> 8 Or Len(Trim(S)) = 0 Then
CountWords = 0
Exit Function
End If
WC = 0
OnASpace = True
For i = 1 To Len(S)
If Mid(S, i, 1) = &quot; &quot; Then
OnASpace = True
Else
If OnASpace Then
OnASpace = False
WC = WC + 1
End If
End If
Next i
CountWords = WC
End Function

-------------------------------------------

Function CutWord(S, Remainder)
'
' CutWord: returns the first word in S.
' Remainder: returns the rest.
'
Dim Temp, P As Integer
Temp = Trim(S)
P = InStr(Temp, &quot; &quot
If P = 0 Then P = Len(Temp) + 1
CutWord = Left(Temp, P - 1)
Remainder = Trim(Mid(Temp, P + 1))
End Function

---------------------------------------

if you need more help...let me know.

 

[redwoodly]

ps. you'll have to modify both to fit your needs, such as CutWords should be modified to cutWords before len(s) = 70 then return the rest...
instead of cut the first word.

 

[CajunCenturion]

Here is a simple function which accepts two parameters, the text string and the maximum length of a single line, and returns a collection of individual lines each less than or equal to the cut-off length.

Here is how you should call it:

Dim IndLines As New Collection
Dim TheString As String

TheString = "Now is the time for all good men to come to the aid of their country"
Set IndLines = SplitTheString(TheString, 25)

and here is the function

Private Function SplitTheString(rStr_InStr As String, rInt_CutOff As Integer) As Collection

   Dim lCol_IndLines       As Collection
   Dim lStr_WrkString      As String
   Dim lInt_SpaceLoc       As Integer
   
   Set lCol_IndLines = New Collection
   lStr_WrkString = Trim(rStr_InStr)
   Do While (Len(lStr_WrkString) > rInt_CutOff)
      lInt_SpaceLoc = InStrRev(lStr_WrkString, " ", rInt_CutOff + 1)
      If (lInt_SpaceLoc > 0) Then
         lCol_IndLines.Add Left(lStr_WrkString, (lInt_SpaceLoc - 1))
         lStr_WrkString = Mid(lStr_WrkString, (lInt_SpaceLoc + 1))
      Else
         lCol_IndLines.Add lStr_WrkString
         lStr_WrkString = vbNullString
      End If
   Loop
   If (Len(lStr_WrkString) > 0) Then
      lCol_IndLines.Add lStr_WrkString
   End If
   
   Set SplitTheString = lCol_IndLines

End Function

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Helen267]

Thanks very much for the responses so far - both look like they'll do what I want. However, I'm pretty new to both Access and Visual Basic, and I haven't used Collections before. How do I get the formatted lines back in to my report? I have 15 fields, named Expr1 - Expr15?

Thanks

Helen