Split function 3

[kupe]

Anyone spare an example of the split function in action that this despairing VB new-ish-bie could download please?

 

[AccessAce]

Here's a small example:

Public Sub TestSplit()
Dim arr
Dim t As Variant

arr = Split("h,i,t,h,e,r,e", ",&quot

For Each t In arr
Debug.Print t
Next t

End Sub

 

[Norris68]

As there isn't an example in the help we'll let you off

Dim S() As String 'An array of strings
S() = Split("This is a string"," &quot 'Splits it up based on spaces (the second parameter)

Now the array is as follows:
S(0) = "This"
S(1) = "is"
S(2) = "a"
S(3) = "string"

We can iterate through them
Dim A As Long
For A = LBound(S) To UBound(S)
Debug.Print S(A)
Next A

Another example:
Dim S() As String
Dim T As String
T = "This is a string, a fairly long string"
S() = Split(T,",&quot 'This time split on the comma

Now, S() is:
S(0) = "This is a string"
S(1) = " a faily long string"

Enough?

 

[JamesHardiman]

This is in Access.

Create a form, place on it a text box (txtMyString), a list box (lstMyList), and a command button (cmdSplit).

Go to the properties of the list box, and set RowSourceType to Value List.

Stick this code behind the button:

Private Sub cmdSplit_Click()
Dim myArray() As String
Dim i As Integer
    myArray = Split(txtMyString)
    For i = 0 To UBound(myArray)
     With lstMyList
        If i = 0 Then
            .RowSource = myArray(i)
        Else
            .RowSource = .RowSource & ", " & myArray(i)
        End If
     End With
    Next i
End Sub

Type "The quick brown fox" (without the quotes) into the text box, and click the button.

myArray is declared as an empty () array of strings. Split splits up the contents of txtMyString, using the spaces as delimiters, and puts each string into myArray. UBOUND gives us the upper boundary of the array (zero-based).

Values in a list-box value list are separated by commas, so that's what's happening in the loop.

Cheers,

James

 

[kupe]

Hi James
Thanks very much. I've done that and get a message box saying string: and giving the string I have typed in.

Then an error message: Object required, and the line highlighted is .RowSource = myArray(i)

I see nothing about a message box in the code. Any thoughts? Cheers

 

[kupe]

Dear Norris68

It must be the time of the day, and the day. But I have a table-full of short sentences to reduce to words that will go into separate fields. Yet I can't see how I link the two things together - the function and the table.

James is aiming me in the right direction, butI'm not there yet.

Very grateful for your coding there, and remarks. Thank you. Cheers

 

[kupe]

AccessAce
Many thanks for the sample. It is appreciated. As you might see, I'm not quite out of woods yet. Cheers

 

[Norris68]

The following will splait TheSentence onto a series of words, then compile an update statement that will update fields Field1..FieldN in MyTable with the individual words.

Dim Words() As String
Dim A As Long
Dim SQL As String

SQL = "UPDATE MyTable SET "
Words() = Split(TheSentence," &quot
For A = 0 To Ubound(Words)
SQL = SQL & "Field" & A & " = '" & Words(A) & "'"
If A < UBound(Words) Then SQL = SQL & &quot;,&quot;
Next A
SQL = SQL & &quot; WHERE Sentence='&quot; & TheSentence & &quot;'&quot;
DoCmd.SetWarnings False
DoCmd.RunSQL SQL 'This runs the SQL statement we just built
DoCmd.SetWarning True

So if you make TheSentence be &quot;Hello world this Friday&quot; you will end up with SQL as &quot;UPDATE MyTable SET Field1='Hello', Field2='world', Field3='this', Field4='Friday' WHERE Sentence='Hello world this Friday'&quot;

It's up to you to adjust to your particular case - you will probably want to make use of the third Split() parameter to limit the number of strings returned if you are putting the result into a table. Otherwise there may be more words than fields to hold them. You don't have to loop through the array, you could use them as you like e.g.

SQL = &quot;UPDATE MyTable SET &quot;
SQL = SQL & &quot;FirstWord='&quot; & Words(0) & &quot;', SecondWord='&quot; & Words(1) & &quot;'&quot;

 

[kupe]

Norris68,
Many thanks. That's looks very good, and thanks for the sql. Very grateful - will be studying it over the weekend. Cheers.

 

[Norris68]

So how many fields have you defined to hold the individual words? What is your table name(s)? Are the sentences in the same table as the individual words or have you defined a separate table?

 

[kupe]

Hi Norris68

Up to 14 fields - as the longest strings have 14 words.
Table name: tblHolKeywords

The sentences in the same table as the individual words - but I could as easily accommodate them in a separate table?

Cheers.