Solved: Parse data from a memo field

[brorick]

I would like to parse information from an Access Memo field. I have the following data in a memo field that I would like to extract the information from and paste the data to their own corresponding fields. For example the account no. 025708 would be parsed and paste to the account no field and so fourth.

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

Account No.: 004453,
customer's Name: Jane Doe,
Bill Payment Method: Monthly,
Last Update: 07-June-2006,

Account No.: 302604,
customer's Name: Bob Willis,
Bill Payment Method: Weekly,
Last Update: 08-June-2006,

Any information is always greatly appreciated. Thank you.

[brorick]

I have found on the web what seems to be a solution to my question. The following was submitted by a SJ McAbney on 11-05-2003.


Public Function GetPart(ByVal strTemp As String, ByVal strDelim As String, _
intPart As Integer) As String

On Error GoTo Err_GetPart

Dim intCounter As Integer
Dim intTotal As Integer
Dim intPos As Integer

Const Message1 = "A delimiter must be a single character."
Const Message2 = "Part exceeds delimited areas."
Const Buttons = vbExclamation + vbOKOnly
Const Title = "Error in Function GetPart()"

If Len(strDelim) <> 1 Then
MsgBox Message1, Buttons, Title
Exit Function
End If

If Right(strTemp, 1) <> ";" Then strTemp = strTemp & ";"

For intCounter = 1 To Len(strTemp)
If Mid(strTemp, intCounter, 1) = strDelim Then
intTotal = intTotal + 1
End If
Next intCounter

If intPart > intTotal Then
MsgBox Message2, Buttons, Title
Exit Function
End If

For intCounter = 1 To intPart
intPos = InStr(intPos + 1, strTemp, strDelim)
Next intCounter

GetPart = Mid(strTemp, intPos + 1, InStr(intPos + 1, strTemp, strDelim) - (intPos + 1))

Exit_GetPart:
Exit Function

Err_GetPart:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_GetPart

End Function


To call it:

MyValue = GetPart(MyMemoField, vbCr, 0)

The arguments:


strTemp - The string to check;
strDelim - The delimiter;
intPart - The section of string split by the delimiter (starts at 0)


I have worked with VBA coding in Excel but I have not attempted it within Access. Therefore I am not sure where to begin and how I would get this to work in Access so that I can test it and verify it will work with my situation. The name of my memo field is called comments. Any thoughts.

Edited 24-Jun-06 by geekgirlau. Reason: insert vba tags

[OBP]

If you are familiar with Excel, then why not do it in excel and import the parsed data in to Access?

[brorick]

Normally I would take that approach. The Access database is preexisting and is connected to a 3rd party software. Therefore I don not have this as an option. The data that the department is entering in this memo field happens to appear in the format I provided. Due to the limitations of the 3rd party software I have been asked to help the department parse the information. I am basically working with certain boundaries. Does the sample code I provided seem like it would work? : pray2:

[OBP]

brorick, I do not think so, because you are not looking for a single delimiter,
the "," delimits the fields, but within the field you have the ":" delimiting the Actual data that you want to extract. To do so you would need to count back from the "," to the ":" or forward from the ":" to the "," and extract that group of characters in between.
I could probably do it for you but I would use my own "Old Fashioned" BASIC code which is where my ID OBP comes from.
What is the name of the memo field?

[brorick]

Your help is greatly appreciated. The name of the memo field is "comments".

[OBP]

brorick, is the data that you have shown in post#1 in one record or 3 records?
Is there more data after it?
I need to know the exact format to construct the VB.

[brorick]

There are a total of three records. I listed the comments for each of the three records. Example:

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

Record 2 Comments
Account No.: 004453,
customer's Name: Jane Doe,
Bill Payment Method: Monthly,
Last Update: 07-June-2006,

Record 3 Comments
Account No.: 302604,
customer's Name: Bob Willis,
Bill Payment Method: Weekly,
Last Update: 08-June-2006,

[Norie]

The easiest way to do this would be to create a function that takes the data and the field name to look for.

You could then use the Split function to parse out the data for the field.

Function ParseMemo(strMemo As String, strFieldName As String) As String
Dim arr1
Dim arr2
Dim I As Long
arr1 = Split(strMemo, ",")
For I = LBound(arr1) To UBound(arr1)
arr2 = Split(arr1(I), ":")
If Trim(arr2(LBound(arr2))) = strFieldName Then
ParseMemo = arr2(UBound(arr2))
Exit For
End If
Next I
If ParseMemo = "" Then ParseMemo = "Field not found"
End Function

[brorick]

Norie thank you for the code. I look forward to giving it a try. Please forgive my ignorance when it comes to Access. Would I place this code in a Module and if so, how would I refer to it in the form?

[Norie]

Yes this would go in a Module.

I used it in a query like this.

SELECT MemoTest.MemoField, ParseMemo([memofield],"Account No.") AS Expr1, ParseMemo([memofield],"customer's Name") AS Expr2, ParseMemo([memofield],"Bill Payment Method") AS Expr3, ParseMemo([memofield],"Last Update") AS Expr4
FROM MemoTest;

Where I had created a table called MemoTest with a memo field called memofield.

[OBP]

This is an alternative old fashioned method that uses a form to transfer the data. There are message boxes i the VB that are pnly there to show what the VB is finding in terms of the positions of the ":" and the "," and the resultant text.
You will have a problem with the date as Access doesn't recognize it as such, you could use a similar piece of VB to rebuild it as a proper date format.
Sorry the first version had an error of 1 character too many.

[OBP]

I forgot, you will have to delete the data from the fields as I have already parsed it LOL.

[brorick]

Thank you Norie and OBP for your assistance. I attempted to get the query from Norie to work, but I haven't had much success. I am getting a debug error. I am sure it is something I am doing wrong and I will continue to work with it. I did however get a chance to work with OBP's database and it works exactly like I had hoped. This question is for OBP. Is it possible to hide the message boxes and let the code run as is in the background? Sorry for the long delay in my response. I was having system issues.

[OBP]

brorick, just put an apostrophe "'" in front of the msgbox on each of the message box lines. This is the equivalent of the old REM statement in BASIC.
Like this
'msgbox blah blah

Or you can just take those lines out all together.

[Norie]

brorick

What error are you getting?

[mdmackillop]

Hi Norie,
Your code works for me.
Regards
MD

[matthewspatrick]

Here's another possible approach, using the RegExpFind function described in http://vbaexpress.com/kb/getarticle.php?kb_id=841

Add a regular module to your Access project and out the RegExpFind function in it
Use a query like this (assumes you are inserting into a new table):SELECT Replace(RegExpFind(Table1.MemoField,"Account No\.:[^,]*",1,False), "Account No.: ", "") AS AcctNum,
Replace(RegExpFind(Table1.MemoField,"customer's Name:[^,]*",1,False), "customer's Name: ", "") AS CustName,
Replace(RegExpFind(Table1.MemoField,"Bill Payment Method:[^,]*",1,False), "Bill Payment Method: ", "") AS BillPayMethod,
DateValue(Replace(RegExpFind(Table1.MemoField,"Last Update:[^,]*",1,False), "Last Update: ")) AS LastUpdate
INTO Table2
FROM Table1

Of course, you might still be interested in breaking up the customer name into separate fields (which could be hard, depending on things like middle names/initials, two word first names or surnames, etc.). Also, rather than "weekly" or "monthly", you would probably want to have a separate PaymentMethods table with a numeric primary key, and convert the "weekly" or "monthly" text into the appropriate numeric foreign key value...

[brorick]

I know this has already been resolved. I have been out of the office and I just had to respond. Norie and OBP, you both have provided me exactly what I was searching for and managed to effectively accomplish this in two entirely different approaches. Thank you for helping me and most of all teaching me in the process. You both are the best. I also want to thank mdmackillop for providing me with a sample database which helped me understand what I was doing wrong and matthewpatrick for helping to provide other solutions. Have a great weekend.

[OBP]

brorick, thank you for thanking us.
Could you mark the thead as closed please?

[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 -


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)

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?:dunno

[brorick]

I am sorry. "Her code" is a typo. :bow: 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.

:mkay 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. :cool:

[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. : pray2: