Odtnhj

I have MS Access form where the user pastes a string into a field Vars, and I want to reformat that string into a new field so that (a) it retains whole words, and (b) "fits" within 70 columns.

Specifically, the user will be cutting/pasting variable names from SPSS. So the string will go into the field as whole names---no spaces allowed---with line breaks between each variable. So the first bit of VBA code looks like this:

Vars = Replace(Vars, vbCrLf, " ")

which removes the line breaks. But from there, I'm stumped---ultimately I want the long string that is pasted in the Vars field to be put on consecutive multiple lines that each are no longer than 70 columns.

Any help is appreciated!

Okay, for posterity, here is a solution:

The field name on the form that captures the user input is VarList. The call to the SPSS_Syntax function below returns the list of variable names (in "Vars") that can then be used elsewhere:

Vars = SPSS_Syntax(me.VarList)

Recall that user input into Varlist comes in as each variable (word) with a line break in between each. The problem is that we want the list to be on one line (horizontal, not vertical) AND a line can be no more than 256 characters in length (I'm setting it to 70 characters below). Here's the function:

Public Function SPSS_Syntax(InputString As String)

InputString = Replace(InputString, vbNewLine, " ") 'Puts the string into one line, separated by a space.

MyLength = Len(InputString) 'Computes length of the string

If MyLength < 70 Then 'if the string is already short enough, just returns it as is.
 SPSS_Syntax = InputString
 Exit Function
End If

MyArray = Split(InputString, " ") 'Creates the array
Dim i As Long

For i = LBound(MyArray) To UBound(MyArray) 'for each element in the array
 MyString = MyString & " " & MyArray(i) 'combines the string with a blank space in between
 If Len(MyString) > 70 Then 'when the string gets to be more than 70 characters
 Syntax = Syntax & " " & vbNewLine & MyString 'saves the string as a new line
 MyString = "" 'erases string value for next iteration
 End If
Next

SPSS_Syntax = Syntax

End Function

There's probably a better way to do it but this works. Cheers.