Solved I need to extract multiple strings from a string in a single field

[prosenkranz]

Hi,

I have made a linked table to an Outlook folder for messages coming from an answering service. The messages contain contents similar to below.

*****************************************************************************************************************************************
CAUTION : This email originated from outside of this organization. Please exercise caution with links and attachments.



STAMP: 3/7/2021 5:15:36 AM - RKS
CALL TYPE: NON-EMERGENCY
CALLER: CHARLIE TUNA
COMPANY: SMALLVILLE
PH: 123-456-7890
ADDRESS: 3
*****************************************************************************************************************************************

I would like to pull the strings (3/7/2021 5:15:36 AM - RKS, NON-EMERGENCY, CHARLES TUNA, etc...) after the labels (STAMP:, CALL TYPE:, CALLER:, etc...) coming from the CONTENTS field, each label text going into a field named after the labels.

How do I format this query to do this?

Thanks,
Phil Rosenkranz

 

[MajP]

This seems to work

Public Function GetPart(memo As Variant, PartName As String) As String
  Dim aPart() As String
  If Not IsNull(memo) Then
    aPart = Split(memo, PartName)
    If UBound(aPart) > 0 Then
      GetPart = aPart(1)
      aPart = Split(GetPart, vbCrLf)
      If UBound(aPart) > 0 Then
        GetPart = aPart(0)
      End If
    End If
   End If
End Function
Public Sub test()
  Dim x As String
  x = "STAMP: 3/7/2021 5:15:36 AM - RKS"
  x = x & vbCrLf & "CALL TYPE: NON-EMERGENCY"
  x = x & vbCrLf & "CALLER: CHARLIE TUNA"
  x = x & vbCrLf & "COMPANY: SMALLVILLE"
  x = x & vbCrLf & "PH: 123-456-7890"
  x = x & vbCrLf & "ADDRESS: 3"
  Debug.Print x & vbCrLf
  Debug.Print GetPart(x, "Address:")
End Sub

In a query it would be something like
Select getPart([yourField],"Stamp:") as Stamp, getPart([yourField],"CALL:") as Call.....

That assumes those are vb carriage return line feeds at the end of each line. If they are not then will have to modify the code.

 

[prosenkranz]

This seems to work

Public Function GetPart(memo As Variant, PartName As String) As String
  Dim aPart() As String
  If Not IsNull(memo) Then
    aPart = Split(memo, PartName)
    If UBound(aPart) > 0 Then
      GetPart = aPart(1)
      aPart = Split(GetPart, vbCrLf)
      If UBound(aPart) > 0 Then
        GetPart = aPart(0)
      End If
    End If
   End If
End Function
Public Sub test()
  Dim x As String
  x = "STAMP: 3/7/2021 5:15:36 AM - RKS"
  x = x & vbCrLf & "CALL TYPE: NON-EMERGENCY"
  x = x & vbCrLf & "CALLER: CHARLIE TUNA"
  x = x & vbCrLf & "COMPANY: SMALLVILLE"
  x = x & vbCrLf & "PH: 123-456-7890"
  x = x & vbCrLf & "ADDRESS: 3"
  Debug.Print x & vbCrLf
  Debug.Print GetPart(x, "Address:")
End Sub

In a query it would be something like
Select getPart([yourField],"Stamp:") as Stamp, getPart([yourField],"CALL:") as Call.....

That assumes those are vb carriage return line feeds at the end of each line. If they are not then will have to modify the code.

MajP,

Thanks for the quick solution! I'm sorry to say that I don't know what to do with this. Are you able to dumb this down a little for me?

Thanks,
Phil

 

[MajP]

You copy and paste he function into a VBA standard module.

Then in the query designer you can make a calculated control like this
Stamp: getPart([yourField],"Stamp:")

Where yourField is the name of your memo field. The first Stamp: is the new name for the calculated column

Query1 Query1

OutlookMessage	Stamp	Caller	CallType
STAMP: 3/7/2021 5:15:36 AM - RKS CALL TYPE: NON-EMERGENCY CALLER: CHARLIE TUNA COMPANY: SMALLVILLE PH: 123-456-7890 ADDRESS: 3	3/7/2021 5:15:36 AM - RKS	CHARLIE TUNA	NON-EMERGENCY

 

[MajP]

I added one extra line of code to remove any spaces on front of back

Public Function GetPart(memo As Variant, PartName As String) As String
  Dim aPart() As String
  If Not IsNull(memo) Then
    aPart = Split(memo, PartName)
    If UBound(aPart) > 0 Then
      GetPart = aPart(1)
      aPart = Split(GetPart, vbCrLf)
      If UBound(aPart) > 0 Then
        GetPart = aPart(0)
      End If
    End If
   End If
   GetPart = Trim(GetPart)
End Function

 

[prosenkranz]

PERFECT! This does exactly what I wanted! Thank you very much!

 

[MajP]

Here are the VBA string functions

String Functions - Visual Basic

Learn more about: String Functions (Visual Basic)

docs.microsoft.com

Often this can be done with Left, Right, Len, INSTR, and Mid. Those are the most important.