parsing memo field 1

[osurfc]

I have a MsAccess XP application that I am revising. There is a memo field that contains text messages. I am trying to split out several pieces of the text and use them to update the corresponding data fields. the text string looks different depending on the individual who entered it.
eg:
Purchased 3/19/06
purchased bulk buy 3/20/06
Purchased
bulk buy $4000 3/19/06
$4230 bulk buy
bulk buy 1/10/06 $4700

As you can see the data is jumbled. I have written an update qry to mark a new field "Status" as Purchased for those items marked as such or bulk buy. The default is consigned. I need to find a way to strip the cost info out and place that in a new field "InvoiceAmt" so that the Dollar amount of owned inventory can be determined. The original app had no way to change the default status or cost @ the time of data entry.

Thanks for any help

 

[acent]

Take a look at the InStr() function. You may consider looping a recordset.

However, the bigger question is the algorithm used to extract the data. Is the above examples almost all of the different ways the information was put in? If so, you may consider just running the code to extract the date given all instances of the first example, then the second, ...

Hope this helps.

"God is a comedian playing to an audience too afraid to laugh."
-- Francois Marie Arouet (Voltaire)

 

[CautionMP]

Here would be a begining. I just dumped the output to the immediate window because I'm not sure how you want get the data into a function or how you want to create the new records.

Sub ParseMemo()
'Concept would be to pass your memo field to the routine

'This is the text from you original post
Const strMemo = "Purchased 3/19/06" & vbCrLf & _
"purchased bulk buy 3/20/06" & vbCrLf & _
"Purchased" & vbCrLf & _
"bulk buy $4000 3/19/06" & vbCrLf & _
"$4230 bulk buy" & vbCrLf & _
"bulk buy 1/10/06 $4700"

Dim intSubString As Integer, intSubStringWord As Integer
Dim txtSubStrings() As String
Dim txtSubSubString() As String
Dim outputDate As Date, outputStatus As String, outputInvoiceAmt As Currency
txtSubStrings = Split(strMemo, vbCrLf)
For intSubString = 0 To UBound(txtSubStrings)
  txtSubSubString() = Split(txtSubStrings(intSubString), " ")
  For intSubStringWord = 0 To UBound(txtSubSubString)
    If IsDate(txtSubSubString(intSubStringWord)) Then
      outputDate = CDate(txtSubSubString(intSubStringWord))
    End If
    If txtSubSubString(intSubStringWord) Like "*$*" Or IsNumeric(txtSubSubString(intSubStringWord)) Then
      outputInvoiceAmt = Mid(txtSubSubString(intSubStringWord), InStr(txtSubSubString(intSubStringWord), "$") + 1)
    End If
    If Not IsDate(txtSubSubString(intSubStringWord)) And Not IsNumeric(txtSubSubString(intSubStringWord)) Then
      outputStatus = outputStatus & txtSubSubString(intSubStringWord)
    End If
  Next intSubStringWord
  Debug.Print outputStatus, outputInvoiceAmt, outputDate
  outputStatus = ""
  outputInvoiceAmt = 0
  outputDate = 0
Next intSubString
End Sub

This is what I got for output:
Immediate
[tt]Purchased 0 3/19/2006
purchasedbulkbuy 0 3/20/2006
Purchased 0 12:00:00 AM
bulkbuy 4000 3/19/2006
bulkbuy 4230 12:00:00 AM
bulkbuy 4700 1/10/2006 [/tt]

You can then take the values from each of the [tt]output[/tt] variables and use them to create your new record.

Hope this helps,
CMP


(GMT-07:00) Mountain Time (US & Canada)

 

[osurfc]

Looking at what I am trying to get (the dollar amount). I think the logic would be to Identify the position of the "$" then identify the position of end of the numeric string (not sure how). Then get the difference between the numeric position. At that point I would have the starting position and the length of the string to parse out. Take that value and plug it into the invoice cost field. All of which would be done in a recordset with an update do loop. Any thoughts on the logic or an easier way to strip out the dollar amount value.

 

[Remou]

If all you want is dollar amounts, a stripped down version of CationMP's code should suit.

Sub ParseMemo()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblMemo")
Dim strMemo, astrMemo
strMemo = Replace(rs!MemoData, vbCrLf, " ")
strMemo = Replace(strMemo, "  ", " ")
astrMemo = Split(strMemo, " ")
For i = 0 To UBound(astrMemo)
    If InStr(astrMemo(i), "$") > 0 Then
        Debug.Print astrMemo(i)
    End If
Next
End Sub