VBA Carriage return after specific amount of characters

[landnw@gmail.com]

Hi,

I have a string that can contain any amount of characters (numbers, letters, etc). When the output is printed, we're limited to only 61 characters per line. I need to be able to have the system force a carriage return after each 61 characters (preferably not cutting off a word). Is this possible in VBA? Many thanks!

 

[MrHans]

Yes, I think this function from Access MVP Ken Schnell is what you are looking for

http://www.accessmvp.com/kdsnell/VBA_Functions.htm#BreakText

 

[StevePupel]

Yes, you can do this in VBA.

I'd probably use a Do While loop and build up a new string which will contain everything in the original string with VbCrLf every n characters. Use the mid function. The second argument is the start location, this will vary, with each iteration through the loop. Define a variable, set it to 1, then with each iteration through the loop increment it by 61.

This will probably be pretty close to working ...

Function FunBuildNewString(BaseString as string) as string
dim StartLocation as long
StartLocation =1
do while StartLocation > 0
if len(basestring) > StartLocation + (61 -1) then
FunBuildNewString = FunBuildNewString & mid(BaseString, StartLocation, 61) & vbcrlf
StartLocation = StartLocation +61
Else
FunBuildNewString = FunBuildNewString & mid(BaseString, StartLocation) & vbcrlf
' If you don't specify the length in the mid function it just grabs the rest of the string.
StartLocation = 0 ' Important! otherwise you'll never get out of the loop.
End If
Loop
end function

 

[The_Doc_Man]

You can get fancier by using repeated InStr functions and slicing out whatever appears between two spaces. InStr will allow you to specify a starting point for your search, which obviously will be one past the ending point of the prior search. But as you slice out each piece and append it (including its space) to your target string, check the length of the part you have already appended and the length of what you are about to append. If that would exceed 61 characters, append the vbCrLf first. At the end of the input string, append one more vbCrLf for good measure.

 

[landnw]

How would I add this to VBA? For example, the control field with the string of text is titled "NOTAMTEXT." I would like the output of the function to be add the new string (with carriage returns) into a new field, which is named "UpdatedText".

Thanks!!

 

[Cronk]

You could put the code on a button's click event or on the control's after_update event or lost focus event or ...