Extracting string from field, need code revision 1

[dkmansion]

Hello all, in a rush I posted this in the wrong place before...

I have a field [memo] which i need to extract multiple sets of data from. I am using mid$(instr()) find the strings but I am having a little trouble getting rid of unwanted data before or after the required string.

The data comes from an email generated from a web system. The length varies as does the number of lines in the body. here is an example:

Reason for rejection: Cancel Report
Other reason from user: This was redictated.

User Login: MDLoginName
User Name: MDName
User MD No.: MD#
User Was: Reporting/Dictating MD

Patient ID: 1234567
Patient MRN: 123456789
Patient Name: lastname, firstname

Result Header ID: 123456789
Dictation Date: 29-OCT-2004 16:13
Report Code: TR105
Report Title: PAIN MANAGEMENT LETTER
Result No: 123456789012

I can get the last string OK with

Mid$(strmemo, InStr(strmemo, "result no: ") + 11)

Which returns
123456789012

My problem is extracting lets say the...
Report code of TR105 and nothing after it. BTW the string after could vary in length from record to record.

I tried this bad code

Mid$(strmemo, InStr(strmemo, "report code: ") + 13)

Which returns
TR105
Report Title: PAIN MANAGEMENT LETTER
Result No: 123456789012

How do I get rid of ALL characters after TR105 without counting said characters after...TR105

Any input would be great. Thank in advance

Donald M

 

[Lightning]

Try this FAQ181-1716

HTH
Lightning

 

[VBslammer]

Make a public function to do the parsing so you can call it from anywhere in your project. Regular expressions are good for finding complex patterns in a string, but VB has some very fast built-in methods that can extract substrings without much work:

Dim strReportCode As String

strReportCode = GetMemoElement(Me!MemoField, "Report Code:")

Parsing function:

Public Function GetMemoElement(ByVal strMemoField As String, ByVal strElement As String) As String
On Error GoTo ErrHandler
  Dim buffer As Variant

  buffer = Split(strMemoField, vbCrLf)
  buffer = Filter(buffer, strElement)
  If UBound(buffer) = -1 Then
    GetMemoElement = ""
  Else
    GetMemoElement = Trim(Replace(Trim(buffer(0)), strElement, ""))
  End If
ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[dkmansion]

Thanks VBSlammer this is perfect. I figured out another way which took alittle more work last night by adding left() to my original code [ left(mid$(instr())) ]but that was returning line returns also.

Thanks much.

Donald M