Solved Split String

[tmyers]

I need a little assistance in coming up with a formula to split a string apart based on length. This is part of a module to export a recordset to an Excel file template for import into my companies system.

If IsNull("Description") then
    'do nothing'
Else
    If Len("Description") > 60 then
        'split string'
    End if
End if

My field has a cap of 60 characters, so if the threshold is reached, I need to split the string into a second row but I obviously don't want to cut words in half, so I need to back track to the first space or " " and split the string there. I am very bad when it comes to using string functions (len, left, right, etc) as I dont have much experience with them.

 

[theDBguy]

Check out this other thread.

Solved - Splitting a Long String into Multiple Lines

Hello, Hope you are doing well. I have a pdf/word form which has description text to be entered into three lines therefore I want to split the description string into multiple lines where each line consist of fixed number of characters say 30chrs. Is there a function available which can split a...

www.access-programmers.co.uk

 

[tmyers]

That looks like it will have the answer for this thanks!

 

[theDBguy]

Good luck!

 

[Gasman]

I am very bad when it comes to using string functions (len, left, right, etc) as I dont have much experience with them.

Well they do say practice makes perfect?

 

[tmyers]

Well they do say practice makes perfect?

Indeed it does!
The main problem I was grappling with was not cutting words into pieces but am reading that thread now. I like the module JDraw shared. The one I currently use to deal with strings that are too long doesnt care about cutting words apart as that is just how it is done in the system I am importing into for this field.

    If Len(rs("CatalogNo")) > 60 Then
        vCatalog = Left(rs("CatalogNo"), 60)
        vDesc = Mid(rs("CatalogNo"), 61)
    End If

 

[Gasman]

Well you could always just concatenate them back together again for display, and no one would any the wiser?