[AccessD] Reformatting a table's data

Here is what the split code looks like roughly

Public Function SplitVB5(Expression As String, Optional ByVal Delimiter 
As String = "  ", Optional ByVal Limit As Long = -1, Optional ByVal 
Compare As CompareMethod = BinaryCompare) As Variant

'Expression:    The string to split.
'Delimiter:     The delimiter used for the split.
'Limit:         The max number of elements to return (-1 = all elements).
'Compare:       The compare method (binary or text).

'Returns:       A zero-based variant array of substrings or
'               entire expression as element(0) if no delimiter found.

Dim lPos1   As Long
Dim lPos2   As Long
Dim lIdx    As Long
Dim lCnt    As Long
Dim saTmp() As String

    'Initialize the variables
    lCnt = 0
    lPos1 = 1
    ReDim saTmp(99)
   
    'Search for the delimiter.
    lPos2 = InStr(1, Expression, Delimiter, Compare)
    While lPos2 > 0 And ((lCnt <= Limit) Or (Limit = -1))
        'Delimiter found, extract the substring between the delimiters.
        saTmp(lCnt) = Mid$(Expression, lPos1, lPos2 - lPos1)
        lCnt = lCnt + 1
        If (lCnt Mod 100) = 0 Then
            'Increase array size if needed.
            ReDim Preserve saTmp(UBound(saTmp) + 100)
        End If
        'Move to end of last delimiter found.
        lPos1 = lPos2 + Len(Delimiter)
        'Search for the next delimiter.
        lPos2 = InStr(lPos1, Expression, Delimiter, Compare)
    Wend
   
    If lPos1 < Len(Expression) Then
        'Extract last substring.
        saTmp(lCnt) = Mid$(Expression, lPos1)
        lCnt = lCnt + 1
    End If
   
    'Resize the array to correct size.
    If lCnt > 0 Then
        ReDim Preserve saTmp(lCnt - 1)
    Else
        ReDim saTmp(-1 To -1)
    End If
   
    'Return the array.
    SplitVB5 = saTmp
   
End Function

Drew Wutka wrote:

> It's not too difficult to write, just a pain.  The trick is that when 
> you split it, you need to keep track of what you pulled.  Since you 
> aren't using 97, you don't need to worry about it.
>  
> Drew
>
>     -----Original Message-----
>     From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com]
>     Sent: Wednesday, February 05, 2003 3:26 PM
>     To: accessd at databaseadvisors.com
>     Subject: RE: [AccessD] Reformatting a table's data
>
>     Drew -
>
>     Thanks for the code.
>
>     I was pulling my hair out trying to create my own 'split' function.
>
>      
>
>     Mark
>
>      
>
>     -----Original Message-----
>     From: Drew Wutka [mailto:DWUTKA at marlow.com]
>     Sent: Wednesday, February 05, 2003 2:35 PM
>     To: 'accessd at databaseadvisors.com'
>     Subject: RE: [AccessD] Reformatting a table's data
>
>      
>
>     Use this code:
>
>      
>
>     Dim rs As ADODB.Recordset
>     Dim rs2 As ADODB.Recordset
>     Dim ExceptionArray() As String
>     Dim i As Long
>     Set rs = New ADODB.Recordset
>     Set rs2 = New ADODB.Recordset
>     rs.Open "tblOriginal", CurrentProject.Connection, adOpenKeyset,
>     adLockReadOnly, adCmdTableDirect
>     rs2.Open "tblFinal", CurrentProject.Connection, adOpenKeyset,
>     adLockOptimistic, adCmdTableDirect
>     If rs.EOF = False Then rs.MoveFirst
>     Do Until rs.EOF = True
>         ExceptionArray = Split(rs.Fields(1).Value, ",")
>         For i = 0 To UBound(ExceptionArray)
>             rs2.AddNew
>             rs2.Fields(0).Value = rs.Fields(0).Value
>             rs2.Fields(1).Value = Val(ExceptionArray(i))
>             rs2.Update
>         Next i
>         rs.MoveNext
>     Loop
>     rs.Close
>     Set rs = Nothing
>     rs2.Close
>     Set rs2 = Nothing
>
>      
>
>     Of course change the table names.  I can send you a sample db with
>     this code in it.  Boy I'm glad you are using A2k (and that's a
>     rare thing, cause I hate A2k....) because I didn't feel like
>     writing out a split function....<VBG>
>
>      
>
>     Drew
>
>         -----Original Message-----
>         From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com]
>         Sent: Wednesday, February 05, 2003 1:18 PM
>         To: accessd at databaseadvisors.com
>         Subject: RE: [AccessD] Reformatting a table's data
>
>         Drew -
>
>         I'm using Access2000.
>
>         I looked up the Split function, but am a bit confused.
>
>         Do you have an example of this?
>
>          
>
>         Thanks,
>
>         Mark
>
>          
>
>         -----Original Message-----
>         From: Drew Wutka [mailto:DWUTKA at marlow.com]
>         Sent: Wednesday, February 05, 2003 2:08 PM
>         To: 'accessd at databaseadvisors.com'
>         Subject: RE: [AccessD] Reformatting a table's data
>
>          
>
>         Sounds like the split function is what you want.  What version
>         of Access are you using.  97 doesn't have the split function.
>
>          
>
>         Drew
>
>             -----Original Message-----
>             From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com]
>             Sent: Wednesday, February 05, 2003 12:53 PM
>             To: AccessD at databaseadvisors.com
>             Subject: [AccessD] Reformatting a table's data
>
>             I have the following 3 records:
>
>             ClaimNo            ExceptionCodes
>
>             1                      1,2,3
>
>             2                      9,10
>
>             3                      1,11,20
>
>              
>
>             What I am looking to do is reformat the table, so the
>             records are easier to work with.
>
>             I would like my result to look like this:
>
>             ClaimNo            ExceptionCode
>
>             1                      1
>
>             1                      2
>
>             1                      3
>
>             2                      9
>
>             2                      10
>
>             3                      1
>
>             3                      11
>
>             3                      20
>
>              
>
>             Any ideas?
>
>              
>
>             TIA,
>
>             Mark Boyd
>
>             Sr. Systems Analyst
>
>             McBee Associates, Inc.
>
>              
>