check length of string and split at 1000 characters and add and replace text

[Jaye7]

OK, so this involves an access script sql but I am converting it to vba.

I have seen where this can be done using 2 textboxes and a commandbutton, but it does not process it the way that I want.

The string is something like the following and it placed in textbox1 and the result should be in textbox2.

SELECT [PO - Stock items ordered].POID, [Stk - Stock items and Suppliers].StkItemID, [PO - Purchase orders].PODate, [PO - Stock items ordered].POSIDateDue, [PO - Purchase orders].CASID, [PMatCatDesc] & " " & [SMatCatDesc] & " " & [SizeDesc] & " " & [ColDesc] & " " & [SysDesc] AS Description, [CS - Client and Suppliers].CASCompanyName 
FROM ((((((([PO - Purchase orders] INNER JOIN ([PO - Stock items ordered] INNER JOIN [Stk - Stock items and Suppliers] ON [PO - Stock items ordered].SAMID = [Stk - Stock items and Suppliers].SAMID) ON [PO - Purchase orders].POID = [PO - Stock items ordered].POID) INNER JOIN [Stk - Stock items master] ON [Stk - Stock items and Suppliers].StkItemID = [Stk - Stock items master].StkItemID) INNER JOIN [Stk - Stock category - Primary] ON [Stk - Stock items master].PMatCatID = [Stk - Stock category - Primary].PMatCatID) INNER JOIN [Stk - Stock category - Secondary] ON [Stk - Stock items master].SMatCatID = [Stk - Stock category - Secondary].SMatCatID) INNER JOIN [Stk - Stock category - Size] ON [Stk - Stock items master].SizeID = [Stk - Stock category - Size].SizeID) INNER JOIN [Stk - Stock category - System] ON [Stk - Stock items master].SysID = [Stk - Stock category - System].SysID) 
INNER JOIN [Stk - Stock category - Colour] ON [Stk - Stock items master].ColID = [Stk - Stock category - Colour].ColID) INNER JOIN [CS - Client and Suppliers] ON [Stk - Stock items and Suppliers].CASID = [CS - Client and Suppliers].CASID WHERE ((([PO - Stock items ordered].POID)=[forms]![OrderInfo]![POID]) AND (([Stk - Stock items and Suppliers].StkItemID)=[forms]![OrderInfo]![ODC5Code]));

Now this is what I am after.

First, replace any single quotes with doubles.

Now test the length of the string and if it is greater than 1000 characters then find the last comma before the 1000 characters, from that comma splt from there on into the next line of the textbox2.
Then it would have to loop the 2nd line checking for 1000 characters etc....

Once it has finished with that then it will add single quotes to each line of the result and " & _ to the end of each line.

Then for the very last line in the textbox it replaces " & _ with ; "

I am not opposed to this being processed in a spreadsheet, I can do most of it but not the splitting each line at the last comma before 1000 characters.

 

[Jaye7]

Hi Rick, sorry, yes you have handled the line continuations correctly, it was just the fact that the sql was multiple lines that was throwing the script out.
With the semicolon already at the end it would just have to add a single quote to the end so that it would finish as ; " rather than how the other separated lines finish with " & _

 

[Jaye7]

Thanks for your help and patience Rick,

I used the following, which does the job.

I changed it to 900 characters as I realized that I have to put something like forms!stock.recordsource = at the start of the script and sometimes it can be quite long.

Private Sub CommandButton1_Click()
Dim test1 As String
test1 = Me.TextBox1.Value
Me.TextBox1.Text = test1

TextBox2.Value = SplitText(TextBox1.Text, 900)

End Sub

Function SplitText(TextBoxText As String, MaxChars) As String
  Dim Space As Long, Text As String, TextMax As String
  Text = Replace(TextBoxText, """", """""")
  Do While Len(Text) > MaxChars
    TextMax = Left(Text, MaxChars + 1)
    If Right(TextMax, 1) = " " Then
      SplitText = SplitText & RTrim(TextMax) & """ & _" & vbLf & """"
      Text = Mid(Text, MaxChars + 2)
    Else
      Space = InStrRev(TextMax, " ")
      If Space = 0 Then
        SplitText = SplitText & Left(Text, MaxChars) & """ & _" & vbLf & """"
        Text = Mid(Text, MaxChars + 1)
      Else
        SplitText = SplitText & Left(TextMax, Space - 1) & """ & _" & vbLf & """"
        Text = Mid(Text, Space + 1)
      End If
    End If
  Loop
  SplitText = """" & SplitText & Text & """"
End Function

 

[Rick Rothstein]

Hi Rick, sorry, yes you have handled the line continuations correctly, it was just the fact that the sql was multiple lines that was throwing the script out.
With the semicolon already at the end it would just have to add a single quote to the end so that it would finish as ; " rather than how the other separated lines finish with " & _

I thought the other lines were not finishing with "&_ , rather, I thought we opted for the colon inside the quote marks? Assuming that is correct, give this function a try...

Function SplitText(TextBoxText As String, MaxChars) As String
  Dim Space As Long, Text As String, TextMax As String
  Text = Replace(Replace(TextBoxText, vbLf, " "), """", """""")
  Do While Len(Text) > MaxChars
    TextMax = Left(Text, MaxChars + 1)
    If Right(TextMax, 1) = " " Then
      SplitText = SplitText & RTrim(TextMax) & ": """ & vbLf & """"
      Text = Mid(Text, MaxChars + 2)
    Else
      Space = InStrRev(TextMax, " ")
      If Space = 0 Then
        SplitText = SplitText & Left(Text, MaxChars) & ": """ & vbLf & """"
        Text = Mid(Text, MaxChars + 1)
      Else
        SplitText = SplitText & Left(TextMax, Space - 1) & ": """ & vbLf & """"
        Text = Mid(Text, Space + 1)
      End If
    End If
  Loop
  SplitText = """" & Replace(SplitText & Text, " :", ":") & """"
End Function

 

[Jaye7]

Sorry, I did want the & _, I didn't want 2 semicolons at the end which is fixed.

Just one more thing, now I need a script to remove single quotes from the start of each line in the textbox, as I am making one to reverse the other, I can replace the quotes etc... myself but I am not sure how to remove the quotes from the start of each line.

 

[Rick Rothstein]

Thanks for your help and patience Rick,

I used the following, which does the job.

I changed it to 900 characters as I realized that I have to put something like forms!stock.recordsource = at the start of the script and sometimes it can be quite long.

Private Sub CommandButton1_Click()
Dim test1 As String
test1 = Me.TextBox1.Value
Me.TextBox1.Text = test1

TextBox2.Value = SplitText(TextBox1.Text, 900)

End Sub

Function SplitText(TextBoxText As String, MaxChars) As String
  Dim Space As Long, Text As String, TextMax As String
  [B][COLOR="#FF0000"]Text = Replace(TextBoxText, """", [/COLOR]"""""")[/B]
  Do While Len(Text) > MaxChars
    TextMax = Left(Text, MaxChars + 1)
    If Right(TextMax, 1) = " " Then
      SplitText = SplitText & RTrim(TextMax) & """ & _" & vbLf & """"
      Text = Mid(Text, MaxChars + 2)
    Else
      Space = InStrRev(TextMax, " ")
      If Space = 0 Then
        SplitText = SplitText & Left(Text, MaxChars) & """ & _" & vbLf & """"
        Text = Mid(Text, MaxChars + 1)
      Else
        SplitText = SplitText & Left(TextMax, Space - 1) & """ & _" & vbLf & """"
        Text = Mid(Text, Space + 1)
      End If
    End If
  Loop
  SplitText = """" & SplitText & Text & """"
End Function

If you change the red highlighted line to this, it will eliminate the internal line feed problem you mentioned in Message #5...

Text = Replace(Replace(TextBoxText, vbLf, " "), """", """""")

 

[Jaye7]

Thanks again Rick, much appreciated