Format Strings

[glove]

If I have a table with a field containing a string such as "Test1;Test2;Test3", is there a way to pull the values from that string, perhaps using the MID function, and display them in a report as:

Test1
Test2
Test3

Any suggestion would be appreciated. Thanks. [sig][/sig]

 

[DougP]

Yes
Do it in a loop like so

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Dim a, findSemicolon, StartPoint As Integer
Dim Yourfield As String
Yourfield = "Test1;Test2;Test3;Test4" I just put this in so I could test it substitue your field for it.
StartPoint = 0
For a = 1 To Len(Yourfield)
findSemicolon = InStr(a, Yourfield, ";&quot
'Debug.Print findSemicolon;
If findSemicolon = 0 Then 'your at the end of the string hop out
Debug.Print Right(Yourfield, Len(Yourfield) - StartPoint)
Exit For
Else
Debug.Print Mid(Yourfield, StartPoint + 1, findSemicolon - StartPoint - 1)
End If
StartPoint = findSemicolon
a = findSemicolon + 1

Next


Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[MichaelRed]

glove,

If you have Ms. Access 2K, I have been told that the Split function has been added to the little old lady's bag of tricks (e.g. intrinsic functions). Otherwise, I did one of my own which may help:

Public Function basSplit(StrIn As String, _
                         Optional DelimChar As String = " ") _
                         As Variant

    'to return an array of the tokens (Words) in a dellimited list of values
    'the delimiter may be set by the user.  The default value for the dilimiter
    'is a single space.  The Delimiter may be set to any string, however only the
    'first character of the string is used.

    'Michael Red, 9/25/00 for the Duvall Group, Columbia, MD
    'Usage & Example

    'MyArray = basSplit("Me, Myself, Thee, Thou, Though, Go, This is a test", ",")
    'For xx = 0 To UBound(MyArray): Print xx, MyArray(xx): Next xx
    '0      Me
    '1       Myself
    '2       Thee
    '3       Thou
    '4       Though
    '5       Go
    '6       This is a test


    Dim Idx As Integer
    Dim Dlm As Integer
    Dim PrvDlm As Integer
    Dim WdsDone As Boolean

    Dim WdAray() As String

    DelimChar = Left(DelimChar, 1)

    Idx = 0                         'Init WdAray Index
    PrvDlm = 0                      'Start w/ Prev pos of Delim Before String
    ReDim WdAray(Idx)               'Initalize array of Words to single element

    While Not WdsDone
        Dlm = InStr(PrvDlm + 1, StrIn, DelimChar)
        If (Dlm = 0) Then     'Can't find any more dellimiters.
            'Must be done.  Just add the remainder of the Input
            WdAray(Idx) = Right(StrIn, Len(StrIn) - (PrvDlm))
            WdsDone = True           'Tell'em were done here
         Else
            'Somewhere in the midst of all this, we jave found a "Real" word
            WdAray(Idx) = Mid(StrIn, PrvDlm + 1, ((Dlm - 1) - (PrvDlm - 1)) - 1)
            Idx = Idx + 1
            ReDim Preserve WdAray(Idx)
            PrvDlm = Dlm
        End If
    Wend

    If (WdAray(Idx) = "") Then
        ReDim Preserve WdAray(Idx - 1)      'Remove (Unused) last array element
    End If

    basSplit = WdAray

End Function

It is pretty self explanatory? If not, repost any questions. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]