Array within a Split Function (1 Viewer)

[jpindi]

I'm looking to optimize a split function by allowing users to enter delimiters into table, and then later on be able to split a string using the delimiters users have chosen. So intead of the delimiter being fixed, it would be flexible depending on what is chosen.

I would get an array via the following:

Do Until Split_table.EOF = True
'add splitter seperator if needed
If Len(Split_Array) > 0 Then Split_Array = Split_Array & ", "

'add splitter address
Split_Array = Split_Array & Split_table("EnterSplit").Value

Split_table.MoveNext
Loop

aSplit = Array(Split_Array)

Normally a part of the code would look like this:

Do Until rstTitles.EOF
astrKeywords = Split(rstTitles!Title, " ")
For intLoop = 0 To UBound(astrKeywords)
' Add each keyword to the temp recordset
rstWordCount.AddNew
rstWordCount!Keyword = astrKeywords(intLoop)
rstWordCount.Update
' Clean up memory
astrKeywords(intLoop) = ""
Next intLoop
rstTitles.MoveNext
Loop

In the second line, the " " would instead be an array (aSplit).

Simply putting aSplit instead of " " doesn't work (error #13 type mismatch) so there is something I'm missing or the function simply doesn't like to have to reference something outside of itself.

Any ideas or am I stretching this function too far? Thanks.

 

[jal]

As I don't know a solution, you might want to parse the string manually (i.e. create your own split function). Parsing isn't easy - takes a lot of code and a lot of debugging to get it right, but it isn't exactly rocket science either. It's pretty doable. Start like this:

Dim strToParse as String, i as Long, C as String
strToParse =rstTitles!Title
for i = 1 to Len(strToParse)
C = Mid(strToParse, i, 1) 'one char
'put code here that checks to see if
'C is a delimiter. If so, start a new word
Next i

 

[gemma-the-husky]

i thought split DID take a string parameter, on which to base the split - so why cant you do this, just by passing in a string variable to the split function

 

[jal]

In VB.Net you can pass in an array of delimiters. The result is multiple delimiters. Seems to me this is what he is trying to do. I could be wrong.

Anyway I doubt VBA has this feature, if that's what he's trying to do.

 

[jpindi]

Hi Jal and Gemma - yes, Jal you are right, looking for multiple delimiters. Ok, I think you're right about VBA simply not liking what I'm trying to do. I'm going to play around with it a little bit more, and if a solution presents itself, I'll post back to this thread.
Thanks for your help,
J pindi

 

[rapsr59]

Hi all!

I think the split function is a method that was designed to take a string of data, with delimiters separating the data that will comprise the elements of the array, and creates an array of that data. The delimiters must be the same character that are used to separate each element.

I don't think there is a way to loop the data, changing the delimeter with each pass, and place each element in an array using the Split function. Each time the loop makes a pass the Split function will only pick up the element that is referenced during that specific pass and it will loose the previously referenced element(s).

However if you can come up with an algorithm that will accomplish this, I would love to see it.

Richard

 

[boblarson]

However if you can come up with an algorithm that will accomplish this, I would love to see it.

Actually, I already have done that for a current project (sort of). I will explain when I can but don't have time right now to post it. I will asap though.

 

[gemma-the-husky]

jpindi

can you provide an example of what you are trying to do

is this a long string or short string?
are theese search phrases?
what are you doing with the entered string?

 

[jpindi]

Hi Gemma ok here are some more details with some code which works. What I want to add to the code is an array (which can be edited via a form). The array will contain the delimiters of the 'split' function. Right now you'll see in the code the only delimiter of the split function is a space (" ").

The msg box is only as a test to make sure everything works. Once everything else is up and running, I'll replace the message box with a different procedure (I'm working on that now). That doesn't have to do with my current problem, though. Here is the code.

Private Sub button_loweredExpectations_Click()
Dim rst1 As ADODB.Recordset
Dim rstTempMem As ADODB.Recordset
Dim cn1 As ADODB.Connection
Dim EachWord1() As String
Dim lenWord1 As Integer
Dim strSQL As String
'set connection
Set cn1 = CurrentProject.Connection
Set rst1 = New ADODB.Recordset
' Build a temporary recordset in memory. you need this to "put" the word you find.
Set rstTempMem = New ADODB.Recordset
With rstTempMem
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Fields.Append "Keyword", adVarChar, 255
.Fields.Refresh
.Open
End With

'select column from table to loop through
strSQL = "Select Title from tblTitles;"
'marry the above selection to the recordset opened
rst1.Open strSQL, cn1, openstatic, adLockOptimistic
'Go to the beginning of the selected recordset

If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveFirst

' Cycle through the titles using the Split function
Do Until rst1.EOF

' parse each title into individual keywords
EachWord1 = Split(rst1!Title, " ")
For intLoop = 0 To UBound(EachWord1)
' Add each keyword to the temporary recordset
rstTempMem.AddNew
rstTempMem!Keyword = EachWord1(intLoop)
'get length of each word
lenWord1 = Len(rstTempMem!Keyword)
' create message box to make sure looping is ok
MsgBox "The word is " & rstTempMem!Keyword & " the length is " & lenWord1
rstTempMem.Update
'clean up memory
EachWord1(intLoop) = ""

Next intLoop
rst1.MoveNext
Loop
End If

' Clean up memory
strSQL = ""
strLastKey = ""
If rst1.State = adStateOpen Then
rst1.Close
End If
Set rst1 = Nothing
If rstTempMem.State = adStateOpen Then
rstTempMem.Close
End If
Set rstTempMem = Nothing
Set cn1 = Nothing
End Sub

 

[gemma-the-husky]

im still not following this - can you give an example of a string, and the delimiters

 

[jpindi]

Ok, so imagine I have a table that is as follows (of course this is a test table):

SKU Title
1 the quick
2 brown fox jumped
3 over the lazy
4 dog

So as it works now, I run the code and a message box pops up saying "the word is the and the length is 3", then click ok and the message box pops up again saying "the word is quick and the length is 5" and so on.

Now imagine in the table you see the following:

SKU Title
1 the-quick
2 brown/fox/jumped
3 over-the-lazy
4 dog/

You see the first two lines are separated by "-" and "/" instead of a space bar. Running the code on this table ends up with "the word is the-quick and the length is 9" which isn't right.

The above is a situation where I need to the code to recognize "-", "/", " " in an array and split words along any of those delimiters.

The question is: Can I put an array in the split function? I'm hearing "no" but boblarson may have found a work around, so we'll see if there is some way of cheating a little bit to see if it will somehow work.

 

[DJkarl]

Ok, so imagine I have a table that is as follows (of course this is a test table):

SKU Title
1 the quick
2 brown fox jumped
3 over the lazy
4 dog

So as it works now, I run the code and a message box pops up saying "the word is the and the length is 3", then click ok and the message box pops up again saying "the word is quick and the length is 5" and so on.

Now imagine in the table you see the following:

SKU Title
1 the-quick
2 brown/fox/jumped
3 over-the-lazy
4 dog/

You see the first two lines are separated by "-" and "/" instead of a space bar. Running the code on this table ends up with "the word is the-quick and the length is 9" which isn't right.

The above is a situation where I need to the code to recognize "-", "/", " " in an array and split words along any of those delimiters.

The question is: Can I put an array in the split function? I'm hearing "no" but boblarson may have found a work around, so we'll see if there is some way of cheating a little bit to see if it will somehow work.

So maybe I'm missing something but it seems like you want to split on a space but need to contend with "/" and "-", have you looked at the replace function?

So instead of this:

EachWord1 = Split(rst1!Title, " ")

You could use this:

EachWord1 = Split(replace(replace(rst1!Title,"-"," "),"/"," "), " ")

That should have the same effect I would think.

 

[boblarson]

Okay, I promised that I would post this. Now, this is just a simple thing that I am doing for a current project, but I can see where it might be strengthend and enhanced to be able to do what you want to do.

We had the issue of trying to have certain data that needed to be inserted into an html document before sending to an email address (sort of like a mail merge). But, the information could change so we wanted to just change it in the table. So, using this function and then by inserting the search term and the replace term, separated by a delimiter and then each pair separated by another delimiter in the single field, we could do so. I know this is not exactly what was asked for, but I think it could head in that direction.

For example in the single field we might have:
{FirstName}|[FName],{LastName}|[LName],{Title}|[ClientTitle]

' if there are replace fields available then replace in the message 
' before inserting to the SQL table 

If Not IsNull(rstLocation!ReplaceFields) Then 
   ' does the first split for the comma delimited values 
   varSplit = Split(rstLocation!ReplaceFields, ",", , vbTextCompare) 
         For intCount = 0 To UBound(varSplit) 
             ' splits the values again to get the value to replace 
             ' and the value to replace with. 
             varSplit2 = Split(varSplit(intCount), "|", , vbTextCompare) 
                   ' checks to see if the replacement has square brackets. 
                   ' if so, it uses it as a field and if not as a literal text string 
                   If Left(varSplit2(1), 1) = "[" Then 
                       ' performs the replacement 
                       strMessage = Replace(strMessage, varSplit2(0), Nz(.Fields(varSplit2(1)).Value), , , vbTextCompare) 
                  Else 
                       strMessage = Replace(strMessage, varSplit2(0), varSplit2(1), , , vbTextCompare) 
                 End If 
        Next 
End If

 

[gemma-the-husky]

a few more questions then

1. how long are lines - is there an upper limit on the number of splits?
2. is any non alpha character to be taken as a separator?
3. what abuot numbers? are they permitted, or are they separators

 

[jpindi]

Hello boblarson - yes your code works well if it is only one thing I am looking to replace. But here is the array of words I want to remove from a table (there is some repetition)

tblWords_to_Delete​

Strings2Eliminate​

[la ]
[ la]
[ & ]
[ (la)]
[ (le)]
[le ]
[ le]
[ les]
[ (les)]
[un ]
[ un]
[une ]
[ une]
[des ]
[ des]
[ ou]
[ou ]
[ où]
[où ]
[ à]
[à ]
[ (des)]
[ (pilot)]
[ i]
[ ii]
[ iii]
[ iv]
[ v]
[ vi]
[the ]
[ the]
[et ]
[ et]
[ and]
[ and]
[ de]
[de ]
[, ]


...I suppose I could write a line of code to handle each one of these, but I was hoping that it would reference a table without going into the code.

Gemma, to answer your questions, (1) the number of lines to check are about 27,000 so it is big. The length of each field is short, however, perhaps topping out at about 27 characters.

Questions (2) and (3) pls see my list of delimiters above.

 

[boblarson]

Yes, I already said that my code was not the exact answer and that it would take some modification to make it work the way you were suggesting. But, that is the key - it will take some WORK to modify it to do it. I, personally, have no desire to go into that much work for something that will not benefit me. So, I leave it to you to do the actual modifications. But it is possible to do using a few loops.

 

[gemma-the-husky]

it begs the question - what is all this for - it seems academic, and not particularly useful
is it some sort of language translator, or some sort of compiler/decompiler?

as an alternative, you might look (not necessarily here) for tokenising text - which is the technique that compilers would use to parse computer code, as this would analyse symbols and suchlike into trees etc - since access/vba doesnt have a pointer type, access wont really be very good for this sort of thing - you could also try googling rpn/reverse polish notation, as that is also connected with such structures i believe

out of my depth really though

 

[jpindi]

Hello - yes this is useful, at least to my client, who, with all due respect, would disagree this is an 'academic' excercise. To clarify, it is a small part of a larger project.

Thank you to those who answered the original question whether or not an array could be used for this. The answer appears to be "no".

Here is my code, which works, except when the datasheet to check gets too big. Any help with this error would be appreciated.

Run-time error '-2147217887 (80040e21)'; File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

And now the code:
Private Sub Button_Replace_Click()
'connection to database
Dim cn1 As ADODB.Connection
'larger table
Dim rst1 As ADODB.Recordset
Dim rstTempMem1 As ADODB.Recordset
Dim EachWord1 As String
Dim strSQL1 As String
Dim num1 As Integer

'this is the table containing the delimiters
Dim rstDel As ADODB.Recordset
Dim rstTempMemDel As ADODB.Recordset
Dim EachWordDel As String
Dim strSQL2 As String
Dim num2 As Integer
'set connection
Set cn1 = CurrentProject.Connection
'assign recordset names (first table, word-to-delete table)
Set rst1 = New ADODB.Recordset
Set rstDel = New ADODB.Recordset

' Build a temporary recordset in memory. you need this to "put" the word from first table you find.
Set rstTempMem1 = New ADODB.Recordset
With rstTempMem1
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Fields.Append "Keyword", adVarChar, 255
.Fields.Refresh
.Open
End With
' Build another temporary recordset in memory. You need this to mark your word-to-delete table.
Set rstTempMemDel = New ADODB.Recordset
With rstTempMemDel
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Fields.Append "Delimiter", adVarChar, 255
.Fields.Refresh
.Open
End With

'select column from larger table to loop through
strSQL1 = "Select Program from SmallerTestSheetAudience"
'marry the above selection to the recordset opened
rst1.Open strSQL1, cn1, openstatic, adLockOptimistic
'select column from word-to-delete table to loop through
strSQL2 = "Select Strings2Eliminate from tblWords_to_Delete"
'marry the above selection to the recordset opened
rstDel.Open strSQL2, cn1, adOpenStatic, adLockOptimistic

'Go to the beginning of the selected recordset

If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveFirst
Do Until rst1.EOF
' Cycle through the word-to-delete
rstDel.MoveFirst
Do Until rstDel.EOF
EachWordDel = rstDel!Strings2Eliminate
'lose the brackets (NB brackets were used in the table _
so that Access doesn't eliminate spaces unnecessarily). _
you might not need the following if these are not included in your table.

EachWordDel = Replace(EachWordDel, "[", "")
EachWordDel = Replace(EachWordDel, "]", "")

'go to the cell where you want to delete the word
EachWord1 = rst1!Program
EachWord1 = Replace(EachWord1, EachWordDel, " ")
rstDel.MoveNext
Loop
'Replace the old cell with the new string
rst1!Program = EachWord1
'clean up memory
EachWord1 = ""

rst1.MoveNext

Loop
End If

' Clean up memory
strSQL1 = ""
strSQL2 = ""
strSQLrep = ""
If rst1.State = adStateOpen Then
rst1.Close
End If
Set rst1 = Nothing
If rstTempMem1.State = adStateOpen Then
rstTempMem1.Close
End If
Set rstTempMem1 = Nothing
If rstDel.State = adStateOpen Then
rstDel.Close
End If
Set rstDel = Nothing
If rstTempMemDel.State = adStateOpen Then
rstTempMemDel.Close
End If
Set rstTempMemDel = Nothing
Set cn1 = Nothing
End Sub