Solved: Parse data from a memo field

[brorick]

My first question is specifically related to Norie's code. Presently the format within the memo field shows the data in a straight line as follows:

Account No.: 025708, customer's Name: John Smith, Bill Payment Method: Weekly, Last Update: 06-June-2006,
customer's Name: John Smith,
Bill Payment Method: Weekly,
Last Update: 06-June-2006,

The problem occurs when I attempt to clean up the appearance of the data in the memo field and place each reference on its own row by selecting the Ctrl and Enter key. When the data looks like the following I get an error message.

Account No.: 025708,
customer's Name: John Smith,
Bill Payment Method: Weekly,
Last Update: 06-June-2006,

Does anyone know why this is occurring? The need to clean the data layout is to make it much easier for a user to enter and update the data.

My second question is related to OBP's code. Is there a way to drop the "," at the end of each field once it has been parsed to its corresponding field. For instance the user currently will see the following:

025708,
John Smith,

Any help is appreciated. Thank you in advance.

[OBP]

Hi again John, I am sorry, I didn't realise that the Comma was showing up in the field.
Has the database changed much since I did the previous work?
What is the error message that you get?

[OBP]

John, the Code to parse without the commas is actually under the command button code in the original database but here it is -

[vba]
Dim count As Integer, count2 As Integer, text As String, field(4), _
length As Integer, fieldcount As Integer
fieldcount = 1
text = Me.comments
length = Len(text)
For count = 1 To length
If Mid(text, count, 1) = ":" Then
For count2 = count To length
If Mid(text, count2, 1) = "," Then
field(fieldcount) = Mid(text, count + 2, count2 - (count + 2))
Exit For
End If
Next count2
fieldcount = fieldcount + 1
End If
Next count
Me.AccountNo = field(1)
Me![Name] = field(2)
Me![Payment Method] = field(3)
Me.Updated = field(4)
[/vba]
I have removed the message boxes.

[brorick]

OBP your updated code did the trick. It works perfectly. I never did receive an error with your code. The error I received was when I used Norie's code. Norie's code works perfectly provided I keep all my information in a straight line. I get the error message the second I seperate the information onto seperate lines by pressing the Ctrl and Enter key and then running the code within the query.

[Norie]

Her code?

[brorick]

I am sorry. "Her code" is a typo. I have since corrected the entry.

[brorick]

Hello OBP. Using your example I am trying to design the following attached database so that the user can parse data from two seperate memo fields in one table on a main form to fields in another table on the subform.

I was not sure how to modify the existing code to accomplish this. I would like to provide the flexibility to permit the user to parse from multiple memo fields if needed (Example 1 up to 10 memo fields) if necessary.

[OBP]

Try this, I just aded a second button for the second memo field, it seems like the easiest way.


If you want to do lots of memo fields I would suggest an Option group to let the user select which memo field to parse and just have on button with as many sets of VBA as necessary. I will help you again when you get all the memo fields that you want to work with. WE can add individual buttons to test each one seperately and then put it all together afterwards.

[brorick]

OBP. I will give it a try. Thank you.

[brorick]

OBP. Is it possible to do away with the button and have the code run at the beggining when the form is opened. If the database contains 20 records then all records would be automatically parsed as described in the current code to the appropriate table and fields. This would free up the user to just view the end result.

[OBP]

Can you email me with what the purpose of the database and this part of in particuar is?

[brorick]

Tony,

The first database I provided to you was just a demo. This attached copy is closer to the working database.

Note: The parsed data tab contains a subform that is not yet properly synched up with on the main form. Therefore to parse the changes made to the other tabs, you need to make sure the subform is reflecting the same record number as the main form and then click on the parsed record button twice. Please let me know if this sample database helps. Thank you.

[OBP]

THis is where I am currently, I am just creating your the code in the form's on Current event procedure.

[OBP]

Give this version a try and let me know what you think. I made some record changes in the memo fields and the VBA picks them OK.

[OBP]

Can you test this version for me.

[brorick]

Hello,

I have attempted to take a different approach with the parsing of data. I decided it would be more efficient and user friendly if all the parsing occurred behind the scenes. As a result I am utilizing a module and incorporating some of the fantastic code that OBP-Tony provided in the previous databases. Unfortunately I am not quite there. I would like to parse the data from each memo field in the tblImport table and paste it to the appropriate fields in the tblImportResults table. I am at a loss.

The following code worked on another test database, but I discovered that this would only work if the data in the memo field was in a straight line separated by commas. (Example: Mr., John, Doe, Big City, TX,). I want to convert this code to achieve the same results using the following memo format.


Salutation: Mr.
First Name: John,
Last Name: Doe,
City: Big City,
State: TX,



Public Sub isSplitValues()


Dim rst As DAO.Recordset, strSQL As String, i As Integer

Dim aseller As Variant

Dim arating As Variant

Dim afeedback As Variant

Dim aprice As Variant

Dim SQLString As String


SQLString = "DELETE * FROM tblImportResult "
DoCmd.RunSQL (SQLString)

Set rst = CurrentDb.OpenRecordset("tblImport", dbOpenDynaset)


If Not (rst.BOF And rst.EOF) Then

rst.MoveFirst

Do

aseller = Split(rst("sellerID"), ",")

arating = Split(rst("feedbackrating"), ",")

afeedback = Split(rst("totalfeedback"), ",")

aprice = Split(rst("price"), ",")

For i = 0 To UBound(aseller)

CurrentDb.Execute "INSERT INTO tblImportResult ( " & "sellerID, feedbackrating, totalfeedback, price ) " & "SELECT '" & Trim(aseller(i)) & "', '" & Trim(arating(i)) & "', '" & Trim(afeedback(i)) & "', " & CCur(Trim(aprice(i))), dbFailOnError

Next i

rst.MoveNext

Loop Until rst.EOF

End If



rst.Close

Set rst = Nothing

MsgBox "done"



End Sub


Any help is greatly appreciated.