[AccessD] Extract data from Single field

[jack drawbridge]

David,

Not sure what you need, but here is an approach (if I understand the
requirement) that may be useful.

Sub david()
    Dim x As String
    Dim i As Integer, j As Integer
    Dim rec As Variant  'terminated with ;
    Dim fld As Variant  'terminated with :

    'put the data template into a string
    x =
"[TransactionDate1]:[Merchant1]:[Amount1]:[ARN1]:[Note1];[TransactionDate2]:[Merchant2]:[Amount2]:[ARN2]:[Note2];"
    rec = Split(x, ";")
    ' act on the rec array
    For i = LBound(rec) To UBound(rec)
        fld = Split(rec(i), ":")
        For j = LBound(fld) To UBound(fld)
            Debug.Print "record (" & i & " , field " & j; ") is " & fld(j)
        Next j
    Next i
End Sub

result is

record (0 , field 0) is [TransactionDate1]
record (0 , field 1) is [Merchant1]
record (0 , field 2) is [Amount1]
record (0 , field 3) is [ARN1]
record (0 , field 4) is [Note1]
record (1 , field 0) is [TransactionDate2]
record (1 , field 1) is [Merchant2]
record (1 , field 2) is [Amount2]
record (1 , field 3) is [ARN2]
record (1 , field 4) is [Note2]

Good luck.

On Wed, Jun 7, 2017 at 7:31 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:

> Hi Bill,
>
> I think the data will not actually include the open and close brackets so
> that shouldn't be a problem.
>
> Since the notes are the last field I just need to deal with ;
>
> Here is what I came up with so far (since there are so few fields and some
> are numbers, some are text, I didn't try to loop individual fields)
>
>             strTransactions = rstDispute!MergedTransactions
>             Do While Len(strTransactions) > 1
>                 strSQL = "INSERT INTO dbo.DisputeTransaction
> (TransactionDate, TransactionMerchant, TransactionAmount, TransactionARN,
> TransactionNotes ) "
>                 strSQL = strSQL & "VALUES ("'"
>                 strSQL = strSQL & basConvertDate(Left$(strTransactions,
> InStr(strTransactions, ":") - 1)) & "','" 'TransactionDate
>                 strTransactions = Right$(strTransactions,
> Len(strTransactions) - InStr(strTransactions, ":"))
>                 strSQL = strSQL & Left$(strTransactions,
> InStr(strTransactions, ":") - 1) & "'," 'TransactionMerchant
>                 strTransactions = Right$(strTransactions,
> Len(strTransactions) - InStr(strTransactions, ":"))
>                 strSQL = strSQL & Left$(strTransactions,
> InStr(strTransactions, ":") - 1) & ",'" 'TransactionAmount
>                 strTransactions = Right$(strTransactions,
> Len(strTransactions) - InStr(strTransactions, ":"))
>                 strSQL = strSQL & Left$(strTransactions,
> InStr(strTransactions, ":") - 1) & "','" 'TransactionARN
>                 strTransactions = Right$(strTransactions,
> Len(strTransactions) - InStr(strTransactions, ":"))
>                 strSQL = strSQL & Left$(strTransactions,
> InStr(strTransactions, ";") - 1) & "'," 'TransactionNotes
>                 strTransactions = Right$(strTransactions,
> Len(strTransactions) - InStr(strTransactions, ";"))
>                 Set cmd = basGetCommand(strSQL, adCmdText)
>                 cmd.Execute
>                 Set cmd = Nothing
>             Loop
>
> Regards
>
> David
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Bill Benson
> Sent: Thursday, 8 June 2017 10:59 a.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Extract data from Single field
>
> What will you do if the Notes contain character like [ or ] or ; or :
>
> ?
>
> What mind of delimiter can be counted on with data like that?
>
> Hi Listers,
>
> I have some data that is currently stored in a single field in the
> following
> format:
>
> [TransactionDate1]:[Merchant1]:[Amount1]:[ARN1]:[Note1];[
> TransactionDate2]:[
> Merchant2]:[Amount2]:[ARN2]:[Note2];etc
>
> The ";" separates records and the ":" separates the data within a record.
>
> Before I start reinventing the wheel, does anyone have a code snippet that
> will extract the fields?
>
> Regards
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>