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

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
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.

Code:
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I just realized that I would need it to not split at the last comma, but at the last space as there may not be many comma's in the line.

the starting string is.

Code:
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]));

ends up as something like

Code:
"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])); "
 
Upvote 0
I think this function will do everything you asked for... give it a try and let me know.

Code:
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

To use it, just assign the output from the function to TextBox2.Value, passing the text from TextBox1 as the first argument and 1000 (max characters per line) as the second argument.

Code:
TextBox2.Value =SplitText(TextBox1.Text, 1000)
 
Upvote 0
Hi Rick, it partially worked.

The result ended up as the following

Code:
"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
[B][COLOR=#ff0000]FROM[/COLOR][/B] ((((((([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)
[B][COLOR=#ff0000]INNER JOIN[/COLOR][/B] [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]))[B][COLOR=#ff0000];;[/COLOR][/B]"

The errors are, when it splits the text to a new line it would have to add the single quotes to the start of the new line, the FROM is a new line and therefore should have quotes and also the INNER JOIN/ 4th output line.

It is also adding two ; at the end of the statement, whereas it should only add 1
 
Upvote 0
Hi Rick, it partially worked.

The errors are, when it splits the text to a new line it would have to add the single quotes to the start of the new line, the FROM is a new line and therefore should have quotes and also the INNER JOIN/ 4th output line.

It is also adding two ; at the end of the statement, whereas it should only add 1
Was there an existing new line at that location already? If so, my code (as written) was not designed to locate it... my function assumes no new lines exist in the text past into it.


It is also adding two ; at the end of the statement, whereas it should only add 1
Did the existing text already have a semicolon at the end? If so, my code (as written) was not designed to look for it.
 
Upvote 0
Hi Rick

Yes the access sql is split into multiple lines, so it would need to merged into one line and then add quotes etc... then split, and yes it does always have a semicolon at the end.

In my spreadsheet code that I was using I was just adding " & _ to every line and then replacing the " & _ with : ", as I didn't know how to do it any other way.
 
Upvote 0
Hi Rick

Yes the access sql is split into multiple lines, so it would need to merged into one line and then add quotes etc... then split
.....
In my spreadsheet code that I was using I was just adding " & _ to every line and then replacing the " & _ with : ", as I didn't know how to do it any other way.
So assuming instead of 1000, your split point was 20 (for space saving consideration), this...

One two three
four five six seven eight nine;

should look like this when done (note where the "four" ended up)...

"One two three four: "
"five six seven eight: "
"nine; "

Is that correct?





...and yes it does always have a semicolon at the end.
So I never have to add the semicolon at the end as it will always be there, correct?
 
Upvote 0
I would prefer 1000 characters, I want to try to combine as much of it into one/each line as possible, it's not the typical access format as it splits it at the SELECT, FROM, WHERE etc..., but that's just how I like it in case I have to manually edit code.
 
Upvote 0
I would prefer 1000 characters, I want to try to combine as much of it into one/each line as possible, it's not the typical access format as it splits it at the SELECT, FROM, WHERE etc..., but that's just how I like it in case I have to manually edit code.
I know you prefer 1000 characters, but I am trying to understand exactly how you want the text parsed (I do not know SQL, so I do not know its syntax) and I am not about to type out a 1000+ characters for the question I asked you, so I used 20 characters so that the text would be small enough to easily see... what I wanted you to do is confirm if how I handled the existing new lines and how I handled the colons instead of the line continuations was correct or not... so, can you do that for me please.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top