[AccessD] Extract data from Single field

[David Emerson]

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