[AccessD] Breaking up a text field

Option Compare Text
'...

Dim strTest As String
strTest = "The stuff that we have " & _
          "is stuff that we don't want" & _
          "and so you can go and stuff it."
Dim i As Integer
Dim avar As Variant
    avar = SplitStr(strTest)
    For i = 0 To UBound(avar)
        Debug.Print avar(i)
    Next i

'...

Public Function SplitStr( _
        ByVal vstr As String, _
        Optional ByVal vstrDlm As String = "stuff") _
        As Variant
Dim avar As Variant
Dim i As Integer
    avar = Split(vstr, vstrDlm)
    avar(0) = avar(0)
    For i = 1 To UBound(avar)
       avar(i) = vstrDlm & avar(i)
    Next i
    SplitStr = avar
End Function

HTH,
Shamil

----- Original Message ----- 
From: <tewald at comcast.net>
To: <accessd at databaseadvisors.com>
Sent: Monday, October 24, 2005 9:50 PM
Subject: [AccessD] Breaking up a text field


> Say we have a text field that has the word "Stuff" in it several times. We
want to break up the field everywhere that word appears, creating new
fields. How can we do that? Putting the data into associated fields in a
separate table would be fine. This seems to me to resemble normalization in
one sense, but I can't quite put my finger on it.
> For example, we have: "The stuff that we have is stuff that we don't want
and so you can go and stuff it."
> We want 4 fields (the first because there is text prior to the first
"stuff"):
> The
> stuff that we have is
> stuff that we don't want and so you can go and
> stuff it.
> Any suggestions?
> TIA,
> Tom Ewald
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com