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.

[vba]
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
[/vba]

To call it:
[vba]
MyValue = GetPart(MyMemoField, vbCr, 0)
[/vba]
The arguments:

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

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?

[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.
[vba]
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[/vba]

[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

1. Add a regular module to your Access project and out the RegExpFind function in it
2. 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?